How to pick a number from a table with 2 references

[csd72]

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?

 

[davidbeach]

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).

 

[ACtrafficengr]

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

for example:

[tt] 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)[/tt]

"...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

 

[dcasto]

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)

 

[bltseattle]

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))

 

[csd72]

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

 

[chichuck]

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

 

[markwazzzzzz]

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 =]

 

[yogia]

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

 

[MortenA]

This was discussed not long ago. Look for almost the same headline as your or use this link:

http://www.eng-tips.com/viewthread.cfm?qid=174397&page=1

Bets regards

Morten

 

[quark]

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))