How Can I Do a Double Lookup?

Hi All;
New to this forum.
I apologize if this topic has been recently discussed, but here it is:

Suppose I have a table of data, several columns and several rows.
Is there a way I can do a vertical lookup based on one value, then from that row of data do a horizontal lookup based on another value, and return a single value?
In other words is there a way I can embed both lookup functions in one cell?

Thanks for any help,
Rod

Here is a simple sheet with the method you need:

Just thinking more about this - do you need to choose the lowest temp value that exceeds your design temp value? For your example would you be looking under the 500 column or the 400 column?
The formula I gave you finds the 400 column - so if you need the 500 column replace the formula in cell I2 with this:

=INDEX(B6:I9,MATCH(C2,A6:A9,0),IF(ISNA(MATCH(F2,B5:I5,0)),MIN(COUNT(B5:I5),MATCH(F2,B5:I5,1)+1),MATCH(F2,B5:I5,0)))

It will return from the column of an exact temp match - otherwise from 1 column to right (last column being the max)

Cutter;
Thanks very much; that works quite well when I copied in your second revised formula.
You interpretted it correctly; when the temperature is between column values it should select the stress corresponding to the next higher column temp value.
The only issue now is that when the design temp is set to an exact column value (eg; 600) it returns ######. One digit less or more works well however.
How can we get it to return the correct value when the temp is an exact column value?

Thanks again,
Rod

HI,

One option is to use "IF". The formula is a bit too long.

=IF(ISERROR(MATCH(F2,B5:I5,0))=TRUE,VLOOKUP(C2,A6:I9,MATCH(HLOOKUP(F2,B5:I5,1,TRUE),B5:I5)+2,FALSE),VLOOKUP(C2,A6:I9,MATCH(HLOOKUP(F2,B5:I5,1,TRUE),B5:I5)+1,FALSE))

You can use the same to the current formula.

@ Rod

I'm not getting that result when I set the temp to 600 or any other exact temp. It is returning the correct value for me.
Copy the formula you're using and paste it here.

One question I still have, though. I placed a condition in the formula to get a value from the last column even if the design temp exceeds 1000. Should it return a warning instead? Like "Design temperature exceeds limit".

Cutter;
When I first copied your formula there is an errant space in the last MATCH function, which rendered it inoperable I guess.
When I corrected that it works just fine!! Thank you very much for the help.

The stress value selected should be up to and including the temp given in the column. ie 550F should return the value for 600 F and so on.
If the design temp is 601, then it should return the value for the next column temp, ie 700F.
So it is working great as you presented it.
Wish I was smart enough to figure that out.

As great as Excel is, I am surprised that there is not an all in one function to accomplish this task.
I have several engineering spreadsheets that can benefit from this functionality, and now that you have shown me the way, I will incorporate this into each, as I get time. I have Larsen's book "Engineering With Excel"; and surprisingly, there is nothing like this in it.
This truly is a great site for Excel users.

Thanks and best regards,
Rod

You're very welcome. Glad I was able to help.

Cutter;
I see what you mean.
I added a new column to the left of the 300F column, with a value of -20F. This represents the stresses at the minimum temp for these materials; ie -20F.
I then expanded the references in the formula to cover the new table size, and it works just fine. Thanks for pointing that out.
If I knew how to attach a file I would do that.

Rod

Instructions for attaching can be found by clicking Forum Rules @ top of page - scroll down to below rule #13

OK I am going to try attaching the file.

Wow, looks like it worked!

Yep, that solves it, too.
You could put Data Validation on cell F2 to warn when too high a number is typed - that would eliminate need for that circumstance in the formula.

Cutter;
Another good idea.
I'll probably give that a try as well.

Thanks,
Rod