×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

How to pick a number from a table with 2 references

How to pick a number from a table with 2 references

How to pick a number from a table with 2 references

(OP)
I have a table with a set of values across the top and another set of values along the side depending what these 2 values are I get another number in the table that I need to retrieve.

for example:

value1 value2
        1      2      3     4
-----------------------------------
1       1.1    1.2    1.4   1.6
2       1.3    1.5     1    1
3       2       2      2    2
4       3       4      5    5  

so if value1 is 2 and value 2 is 2 then I need to retrieve 1.5.

Can anyone help me on how I can retrieve these values?

RE: How to pick a number from a table with 2 references

Look at all the functions with "LOOKUP" in the function name.  You'll need a named range that doesn't include your 1,2,3,4 labels (they can be outside the range for documentation purposes).

RE: How to pick a number from a table with 2 references

in Hlookup, can you give a cell reference for the offset?

for example:

         A        B      C       D       E  
1   |     val1    val2
2   |             1      2      3      4
3   |     1       1.1    1.2    1.4    1.6
4   |     2       1.3    1.5    1      1
5   |     3       2      2      2      2
6   |     4       3      4      5      5  


=hlookup(a3:e6,a1,b1)

     "...students of traffic are beginning to realize the false economy of mechanically controlled traffic, and hand work by trained officers will again prevail." - Wm. Phelps Eno, ca. 1928

"I'm searching for the questions, so my answers will make sense." - Stephen Brust

RE: How to pick a number from a table with 2 references

Without to much work, you could do two lookups.  The first lookup would be to find the column number based on real values on the top row.  Then use that as the column on a VLOOKUP.  

    value1    3.6            
    value2    2            
    Results    1.5            
                    
    3.2    3.6    3.9    5    << VALUE 1
VALUE2  1    2    3    4    Index
1    1.1    1.2    1.4    1.6    
2    1.3    1.5    1    1    
3    2    2    2    2    
4    3    4    5    5    
                    
the cell for Results is 1.5 = VLOOKUP(value2,{range includes value2 index row and data set},HLOOKUP(value1,{range includes Value 1 and index rows},2)+1)

RE: How to pick a number from a table with 2 references

Consider the INDEX function:

INDEX(array,row_num,column_num)

array is the range for your table.

For the row_num and col_num, you can MATCH against the row headers to find which row:

MATCH(lookup_value,lookup_array,match_type)
You will have to read up in Help and decide on the correct match_type for your application.

Your final function would look something like:

INDEX(my_table_range, MATCH(value1, $range_of_row_headers, match_type), MATCH(value2, $range_of_col_headers, match_type))



RE: How to pick a number from a table with 2 references

(OP)
Thanks guys,

I ended up using one lookup to create a separate line below the table relative to the first reference, and a second lookup to pick the relevent one from this line relative to the second refereence.

I am using it to reference a code table for wind loads on signs.

Thanks for the help.

Regards

Craig

RE: How to pick a number from a table with 2 references

csd72,

I do that for looking up wind loads from tables all the time.

This always gives me the next lowest index value.  I usually want to get the next highest one as well and then interpolate between the two lines.  To get the upper limit line for interpolation, I put a second column of index valuse just to the left of my table, with the index values pushed down one line (I add a zero for the first index.  Then I do another vlookup off of the new index column.  (This one looks up the same value as the first one, but it goes to the next line in the table.  This way I get two lines out of the table, and can interpolate between the two.  Its ugly but it works.

regards,

chichuck

RE: How to pick a number from a table with 2 references

There is a very simple way to do this with a VBA function called "getTableValue()"....
Go to this website: http://www.bridgeart.net/spreadsheets/sprd_vba_functions.php
The owner of that site has written a very clever VBA function that will do exactly what you are looking for.
Good luck with your endeavor.
Mark  =]

RE: How to pick a number from a table with 2 references

Hi csd72:

If you are using EXCEL, then you can use the INTERSECT operator (simply a space) to get the result you want.

Let us say in your table, the rows are labeled H_1,H_2,H_3, and H_4; and columns are labeled V_1,V_2,V_3, and V_4

then refering to your table ... H_2 V_2  or  V_2 H_2

would yield the result 2

 

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC
http://www.energyefficientbuild.com

RE: How to pick a number from a table with 2 references

The formula given by Morten is quite effective. I also picked one formula (fianlly) from Walkenbach and here it is as below.

After constructing the table, name the first column as Value1 (including the blank cell) and name the first row as Value2 (including the same blank cell). Then name the entire table including the first row and first column as ValueTable.

Then, if the value1 input is in cell B12 and value2 input is in cell B13 then you can have a two way lookup in B14 as =index(ValueTable,match(b12,Value1,0),match(b13,Value2,0))

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources