Vlookup with 3 criteria and closest match

**martinpgibson** · 03-09-2015, 09:02 PM

I have a document attached where I need to do a vlookup on 3 criteria and I don't know how to go about developing a formula for it.

The first lookup I want to do is on the first column and match it with the value "Emu" and then on the 2 column I want to do a match on the colour "Red" and then with the lines that meet this criteria find the closest number to 76239 in the number column, and then bring back the return value in the next column. So the function in my example would bring back 11. Thanks so much for your help in advance

animals.xlsx

**FlameRetired** · 03-09-2015, 09:58 PM

Does it have to be VLOOKUP?

If it can be else try this array formula.

Formula:

Please Login or Register  to view this content.

Array enter means the formula must be committed by simultaneously pressing and holding down Ctrl and Shift while hitting Enter. Helper cells F4, G4 and H4 contain "Emu", "Red" and 76239.

**martinpgibson** · 03-09-2015, 10:04 PM

Hi FlameRetired, this works fantastically and yeah it certainly doesn't have to be vlookup. I appreciate your help

**AlKey** · 03-09-2015, 10:06 PM

Array entered

=INDEX(D4:D16,MATCH(MIN(IF((A4:A16="Emu")*(B4:B16="Red"),C4:C16)),C4:C16,0))

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER