VLookup with multiple requirements

Johnsie McGuire

I have read several community threads already, and can't seem to find what i am looking for. I have a Vlookup table that includes Pipe Type (Copper or SCH 40), Pipe Size (1, 2, 4, 6), Pipe OD (Different OD based on Pipe type) in 3 separate columns. I want to show the Pipe OD Based off the Pipe Type and then Pipe Size. I have tried a nested VLOOKUP to no avail, I have tried an =if(vlookup to no avail. How can i get this done?

sean59916

Johnsie -

Can you post some screenshots so we can help you out?

sean59916

Heres a quick solution, refer to the screenshots used for 2 sheets - a "table of data" and the end user sheet.

1. In your existing table - add a helper column and concatenate the Pipe Type + Pipe OD columns - eg. =[Pipe Type]1 + [Pipe Size]1 ; or you could use =[Pipe Type]@row+ [Pipe Size]@row
2. In the end user sheet then use something like =INDEX({PIPE OD}, MATCH([Pipe Type]@row + [Pipe Size]@row, {Concat}, 0))

 

Breaking down the INDEX/MATCH above - INDEX(The column you want returned, MATCH(the data to search, where to search, search type)

You could also perform a VLOOKUP which would be =VLOOKUP([Pipe Type]1+[Pipe Size]1, {Table Range 1}, 4, false) - using the VLOOKUP requires the reference column to be left of the results which is not the case with the INDEX/MATCH. 

I hope that helps and explains?

 

Sean

 

Paul Newcome

You could also skip the helper column and use the following...

 

=JOIN(COLLECT({OD Column}, {Type Column}, Type@row, {Size Column}, Size@row))

sean59916

Brilliant Paul. Thanks

Paul Newcome

Sure thing! Now we just need to hope the OP sees the responses.