Lookup help!!

[scott45]

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

 

[mbarron]

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

 

[Phillycheese5]

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

 

[scott45]

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

 

[scott45]

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

 

[scott45]

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

 

[scott45]

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

thanks again scott