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?
Comments
-
Johnsie -
Can you post some screenshots so we can help you out?
-
Heres a quick solution, refer to the screenshots used for 2 sheets - a "table of data" and the end user sheet.
- 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
- 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
-
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!
-
Brilliant Paul. Thanks
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!