VLookup with multiple requirements

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?

Tags:

Comments

  • sean59916
    sean59916 ✭✭✭

    Johnsie -

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

  • sean59916
    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

     

    2019-06-05_13-57-41.jpg

    2019-06-05_13-59-58.jpg

  • Paul Newcome
    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))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • sean59916
    sean59916 ✭✭✭

    Brilliant Paul. Thanks

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!