VLOOKUP Exact Partial Match (Multiple Results)

On Default, VLOOKUP is not case sensitive thus it does not do an Exact match lookup.

It is even harder for VLOOKUP to do an exact partial match lookup and return multiple results.

This article will show you how to resolve the above difficulties by nesting IF, SMALL, EXACT and CHOOSE Functions in VLOOKUP.

Using the data below return all Suppliers who supplied FURNITURE and those whose cost code contains the letters PO.

VLOOKUP EXACT MATCH

=VLOOKUP(
SMALL(IF(EXACT($G$2,Tbl[[Product ]]),ROW(Tbl[[Product ]])),ROW(A1)),
CHOOSE({1,2},ROW(Tbl[[Product ]]),Tbl[Supplier]),
2,
FALSE
)

There are 2 major tricks here:

1. Nest the EXACT function to make VLOOKUP case sensitive
2. Use the Row numbers for items that are an exact match as the lookup values

VLOOKUP PARTIAL MATCH

=VLOOKUP(
SMALL(IF(ISNUMBER(FIND($G$2,Tbl_3[Cost Code])),ROW(Tbl_3[Cost Code])),ROW(A1)),
CHOOSE({1,2},ROW(Tbl_3[Cost Code]),Tbl_3[Supplier]),
2,FALSE)

The only new trick here is to use the FIND function to do a partial case-sensitive search. Then use the ISNUMBER function to convert the results into a boolean array.

DOWNLOAD WORKSHEET

Further Reading

VLOOKUP IS DEAD–OR IS IT?

5 Functions that Return Multiple Results In a Lookup