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?

Find more posts tagged with

Formulas

Comments

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.

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

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Calculating Quarters with a Start and a Finish date
I searched the community and did not see a formula that calculated Quarter based on a Start and a Finish date while also accounting for cases where the year for the Start and Finish date differ. The formula below handles that. A few notes … It assumes that Finish is always >= Start. The entire formula is wrapped with…
Circular Reference with formula
i am not good with writing formulas within a sheet. I asked the AI tool in the sheet to help me write a formula and I am getting a Circular reference back. I'm trying to have it write the following formula for me. If the column "Tracking #" is not blank, change Column "Deployment Phase" to "Shipped" I have tried adding in…
How to delay the execution of a workflow whose trigger occurred much earlier?
I have a workflow that detects a row(date) that is changing its Fiscal Week Finish Date and send this row to other Sheet. Long story short: I collect delayed tasks in separate file & I tag reasons for delay for these tasks. It happens that plan are analyzed 2-3 hours and dates are being changed several times. I would like…