Need to match two columns and return a third column value from a table

**HRGIRL16** · 02-12-2014, 12:52 PM

I have two columns in which I want to match BOTH columns to two columns in a table, and then VLOOKUP return the value from a third column in the table. But I'm not sure if this is possible...

I have attached an example:

On the left, I have two columns of data and the area where I want to have the VLOOKUP. On the right side is the table with 3 columns of data. I want to have A2 and B2 BOTH match to something in columns H and I AND return the value from column J. So if we look in the data, matching A21 & B21 to H & I the value should be Northwest Allied Physicians, but if we only have the one value (like normal VLOOKUP), this doesn't work and it returns OB.

So is there a way to have the VLOOKUP check and match BOTH values before it returns a value?

Thank you!

**daffodil11** · 02-12-2014, 01:19 PM

D2:

Non-Array Formula

=LOOKUP(2,1/(($H$2:$H$22=B2)*($I$2:$I$22=C2)),$J$2:$J$22)

or

Array Formulas

=INDEX($J$2:$J$22,MATCH(B2&C2,$H$2:$H$22&$I$2:$I$22,0))

=INDEX($J$2:$J$22,MATCH(B2,IF($I$2:$I$22=C2,$H$2:$H$22),0))

(array formulas are confirmed with Ctrl+Shift+Enter to exit the cell)

**HRGIRL16** · 02-12-2014, 02:03 PM

Thank you SO much daffodil11. This was perfect...I could have tried all day and not have been able to do this!!

Really appreciate your quick response too!

**dwhiskee** · 06-01-2017, 08:03 PM

Hi Daffodil11,
Would it be possible for you to explain the Lookup formula.
I can't wrap my head around how this works.

thanks!

**FDibbins** · 06-02-2017, 02:18 AM

dwhiskee welcome to the forum

I know this is really just asking how it works, but unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Need to match two columns and return a third column value from a table

LinkBack

Thread Tools

Rate This Thread

Display

Need to match two columns and return a third column value from a table

Re: Need to match two columns and return a third column value from a table

Re: Need to match two columns and return a third column value from a table

Re: Need to match two columns and return a third column value from a table

Re: Need to match two columns and return a third column value from a table

Thread Information

Users Browsing this Thread

Similar Threads

Match 1st and 2nd columns in 2 sheets and return 3rd,4th and 5th column if match.

Match 3 set of columns and return value from another column in case of a unique match.

Excel 2007 : how to match 2 columns and return value from the 3rd column

Match 2 columns in 2 sheets and return the 3rd column

Excel 2007 : Match 2 columns to return the value of the third column

Bookmarks

Bookmarks

Posting Permissions