VLOOKUP with 2 conditions

Nick70

Active Member
Joined
Aug 20, 2013
Messages
299
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have data in Sheet1 range B5:C11 and in range F5:H11

I would like to have a VLOOKUP formula in range D5:D11 so that if in any row of range B5:C11 (eg. B5:C5) the two values contained in the two cells are found in range F5:H11, then we get as result the corresponding text found in column H.

In my example below we see that we have text "A" and "C" in range B5:C5 which is also contained in range F6:G6 for this reason we would expect value orange to appear in cell D5.

See also other example highlighted in yellow.

Basically whereas VLOOKUP looks for same value in two ranges to give a result, I would need TWO matching values in same row to give result.
How do I do that?

Thanks,
N.
Ps. Apologies for not using XL2BB but had issues downloading it

1579359056781.png
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Use the following formula (variant for H5):
=INDEX(H$5:H$11,MAX((B$5:B$11=F5)*(C$5:C$11=D5)*ROWS(B$5:B5))).

Please let me know if it works correctly, because I'm typing it directly here.
Use Ctrl-Shift-Enter to confirm it.

J.Ty.
 
Upvote 0
Hi J.Ty.

The formula should be in range D5:D11.
Because your formula references D5, I get a circular reference.

Which cells do you want me to put your formula?

Can you please have another look?

I need to return values in H5:H11 in range D5:D11 if two values in rows B5:C11 are found in range F5:G11.

Thanks,
N.
 
Upvote 0
How about
=IFERROR(INDEX($H$5:$H$11,MATCH(B5&"|"&C5,INDEX($F$5:$F$11&"|"&$G$5:$G$11,0),0)),"")
 
Upvote 0
Sorry, my fault. Now the formula for D5:
=INDEX(H$5:H$11,MAX((F$5:F$11=B5)*(G$5:G$11=C5)*ROWS(F$5:F5)))
Still Ctrl-Shift-Enter to confirm it.

J.Ty.
 
Upvote 0
How about
=IFERROR(INDEX($H$5:$H$11,MATCH(B5&"|"&C5,INDEX($F$5:$F$11&"|"&$G$5:$G$11,0),0)),"")
If "|" appears in columns, it might produce false results.

J.Ty.
 
Upvote 0
True, but if the OP does have a pipe in their data then it can be swapped for any other character that does not appear.
 
Upvote 0
Hi JTY,

Unfortunately I get in range D5:D11 all values contained in H5:H11 whether the rows are matching or not.

@Fluff your formula works very well.

A big thanks to both!
N.
 
Upvote 0
True, but if the OP does have a pipe in their data then it can be swapped for any other character that does not appear.
Sure. Still, it is playing with delimiters and making solutions whose correctness depends on tacit assumptions about the data. Who will remember 3 years form now that this particular spreadsheet does not handle "|" in certain columns?

J.Tzy.
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top