Three way lookup

[siddiq1212]

attach file
I want values in sheet 2 column type as A-1 .........E1 etc.
Please help

[Glenn Kennedy]

Soory, I don't understand what the result you want is. can you explain a bit more clearly...

[cbatrody]

Hi,

If you are comparing the 'Pipe Diameter' in two tabs, you have multiple 'Type' fields for each Diameter (Sheet1). Please provide some logic in filling up the 'Type' field in Sheet2.

[AliGW]

Hi,

If you are comparing the 'Pipe Diameter' in two tabs, you have multiple 'Type' fields for each Diameter (Sheet1). Please provide some logic in filling up the 'Type' field in Sheet2.

If the pipe diameter falls between the two given on sheet 1, then that's the diameter needed on sheet 2, surely?

[martindwilson]

in sheet2!b6 filled down
=INDEX(INDEX(Sheet1!A:A,MATCH(A6,Sheet1!B:B,0)):INDEX(Sheet1!A:A,MATCH(A6,Sheet1!B:B,0)+3),MATCH(B6,INDEX(Sheet1!C:C,MATCH(A6,Sheet1!B:B,0)):INDEX(Sheet1!C:C,MATCH(A6,Sheet1!B:B,0)+3),1))

[martindwilson]

OOPS just noticed A has more ranges than the rest then last formula wont work
try
=LOOKUP(A6,{300,400,500,600,700,800,900,1000},{"A","B","C","D","E","F","G","H"})&"-"&IF(A6=300,MATCH(B6,{0,1.5,2.5,3,4},1),MATCH(B6,{1.5,2.5,3,4},1))

[siddiq1212]

Thank you very much

[siddiq1212]

Great reply thanks

[sktneer]

Another variation...

On sheet2, in B6, try this array formula which requires confirmation with Ctrl+Shift+Ente instead of just Enter. (i.e. hold down the Ctrl+Shift and then press Enter.)

Please Login or Register  to view this content.