I always have trouble with lookups. I will try and explain what I need. I
have information in two different cells.
cellE22 is 24.5
cell f22 is 0.7

I have a table made across the top this is degrees this is where 24.5 would
be along BP4:Cu4 It starts at <.5 degrees and goes up to 30 degrees although
I do not have it in decimal points just whole numbers 1-2-3-4-5-6-7 etc
In coulmn BP6:bp32 I have the other number I need to look up. It starts at
0.4 and ends at 3.0
In the cells BP4:CU 32 are the set values
I need to lookup the set value of 0.7 in realationship with 24.5 degrees. I
hope I have explained this correctly. Is this possible to do. To lookup two
different values and return the set value.
Thanks in adavance
scott

You can do this by combining the Index and Match functions
=INDEX(your range,MATCH(row value,rows to look in),MATCH(column
value,columns to look in))


It looks like you formula would be:
=index(BP4:CU32,match(F22,BP4:BP32),match(E22,BP4: CU4))

Hey this helps allot but will it make a diffenrence If my degrees (row across
the top) is not the same. I have it in whole degrees and not in tenths?
Because The number that I am returning with your formula is not correct. But
I am closer than I have ever been. I will keep working also. Thanks Scott

"mbarron" wrote:

You can do this by combining the Index and Match functions
=INDEX(your range,MATCH(row value,rows to look in),MATCH(column
value,columns to look in))


It looks like you formula would be:
=index(BP4:CU32,match(F22,BP4:BP32),match(E22,BP4: CU4))

Hey thanks allot I was looking at your formula and I typed in one wrong
column it works perfect thanks so much. I am however not real sure on
understanding the index and match but thanks again

"mbarron" wrote:

You can do this by combining the Index and Match functions
=INDEX(your range,MATCH(row value,rows to look in),MATCH(column
value,columns to look in))


It looks like you formula would be:
=index(BP4:CU32,match(F22,BP4:BP32),match(E22,BP4: CU4))

One more question. On this formula if there is not information in g22 or f22
I get a #NA, is there a way to hide this

Thanks scott


"mbarron" wrote:

You can do this by combining the Index and Match functions
=INDEX(your range,MATCH(row value,rows to look in),MATCH(column
value,columns to look in))


It looks like you formula would be:
=index(BP4:CU32,match(F22,BP4:BP32),match(E22,BP4: CU4))

Hey thanks again I took care of my error I just stuck an If function in there
to show 0

thanks again scott


"mbarron" wrote:

You can do this by combining the Index and Match functions
=INDEX(your range,MATCH(row value,rows to look in),MATCH(column
value,columns to look in))


It looks like you formula would be:
=index(BP4:CU32,match(F22,BP4:BP32),match(E22,BP4: CU4))

If I understand it correctly, you basically are looking for a single
value if given a value in the column and a value in a row (like a
multiplication table). In that case, you can use the following
formula:

in general:
=INDEX(table range including headers,MATCH(value in column,column
range,0),MATCH(row value,row range,0))

in your case:
=INDEX($BP$4:$CU$32,MATCH(value in column,$BP$4:$BP$32,0),MATCH(row
value,$BP$4:$CU$4,0))

...where "value in column" and "value in row" are the inputs to get an
intersection data point. You can make them cell sepcific so a user can
type the row value in A1 and column value in A2.

Hope this helps,
Phillycheese


--
Phillycheese5
------------------------------------------------------------------------
Phillycheese5's Profile: http://www.excelforum.com/member.php...o&userid=24196
View this thread: http://www.excelforum.com/showthread...hreadid=480109