Two variable look up from a table

[krlss26]

Hello!

I have been trying to solve this problem myself for a long time, but have failed to do so, I'll have to admit.

I have a A1:AH14 table with values to specify the "schedule" of pipes depending on its thickness and diameter.

I need to find that schedule with two values, as I said: thickness and diameter. The problem is that the thickness value is within the table and has to be located under the diameter that is given. Normally, the value of thickness is not exact as shown on the table, so the next available value has to be taken.

I am attaching my table, so you get my point.

Table.xlsx

Please help!

[Richard Buttrey]

Hi,

Since the values in the table are not in ascending order in each column this won't be easy with normal MATCH functions which require lists of values to be in order.

It would be much simpler if you create a different layout for your table like the one below. Then the solution will be a simple VLOOKUP

Please Login or Register  to view this content.

The Schedule refs are before the _ and the diameters afterwards.

[FDibbins]

This calcs what (i think) you want...
=INDEX($A$3:$A$15,MATCH(B18,OFFSET(A1,2,MATCH($B$19,$B$1:$AH$1,0),13,1),1)+1)

However, I would question how you arrive at your answer of 120 (A12), when the value you are searching for is 1.228 - which is less than 1.281, which would give you 100?

[TMS]

I don't think your model will work. In your example, you say that you expect the schedule to be 120. However, 1.228 is less than 1.281 which is against schedule 100. 1.500 would return schedule 120.

You also have duplicate values in the column. For example, you have two (2) 0.500 values. So it would be impossible to match against the second one, whatever mechanism you use to match values.

Regards, TMS

[TMS]

@Ford: neat formula, though I am not a fan of OFFSET. But it seems to do the job.

That said, there are lot's of opportunities for #N/A (eg: 22 and 0.249-) and #REF! (eg: 22 and 2.126+) errors.

Regards, TMS

[PietBom]

I used a few helper formulas: Match, Indirect and Index

[FDibbins]

@Ford: neat formula, though I am not a fan of OFFSET. But it seems to do the job.

That said, there are lot's of opportunities for #N/A (eg: 22 and 0.249-) and #REF! (eg: 22 and 2.126+) errors.

Regards, TMS

@ Trevor, thanks, and I agree completely, I try and avoid OFFSET if I can and yes, there are some bugs in it, but I think they will return errors rather than wrong answers (except where they want the 2nd value of duplcates)

[krlss26]

It was my mistake, I meant the opposite, the value of 1.228 should go with 1.281!

[krlss26]

This is homework for a non-computer-related class. I've done a lot of formulas (for the first time in excel) and this is the last part, which has been impossible to me.

[Haseeb Avarakkan]

Hello Kriss,

Try this array formula with CTRL+SHIFT+ENTER,

=INDEX(A3:A15,MATCH(TRUE,INDEX(B3:AH15,,MATCH(B19,B1:AH1,0))>=B18,0))

[krlss26]

Thank you so much everyone! You are really good!!