Vlookup with condition

VLOOKUP and a condition

The animated picture above shows you how the array formula removes specific records in the the table_array argument.

Array formula in C4:

=VLOOKUP(C2,IF(B8:B17=C3,A8:E17,""),3,FALSE)

becomes

=VLOOKUP("A",{"", "", "", "", "";"", "", "", "", "";"", "", "", "", "";"", "", "", "", "";"A", "South America", "The New Firm", "Tory", "Byrnes";"", "", "", "", "";"", "", "", "", "";"", "", "", "", "";"", "", "", "", "";"F", "South America", "Trans United Airways", "Ervin", "Hennessey"},3,FALSE)

and returns "The New Firm"

How to create an array formula

1. Select cell C4
2. Type the array formula
3. Press and hold Ctrl + Shift
4. Press Enter