How to make the VLOOKUP function case sensitive.(Microsoft Excel)

Japanese version.

The VLOOKUP and XLOOKUP functions ignore case when searching.

Therefore, the intended result may not be obtained.

Here is how to solve that problem.

Steps

IF, SUMPRODUCT, EXACT, INDEX, and ROW functions are used.

The cell range must be specified starting with the first row.

This will result in a case-sensitive search.

=IF(SUMPRODUCT(EXACT(Search cell range, Search value)*1)>0,INDEX(Result cell range,SUMPRODUCT(EXACT(Search cell range,Search value)*ROW(Search cell range))),"")

=IF(SUMPRODUCT(EXACT($B$1:$B$6, E3)1)>0,INDEX($C$1:$C$6,SUMPRODUCT(EXACT($B$1:$B$6,E3)ROW($B$1:$B$6))),"")

If the LET function is available.

The LET function can be used to exclude duplicate cell designations in the search range.

This saves time and reduces the risk of making a mistake when changing reference cells.

=LET(
  SearchCellRange,$B$1:$B$6,
  ResultCellRange,$C$1:$C$6,
  SearchValue,E3,
  IF(
     SUMPRODUCT(EXACT(SearchCellRange,SearchValue)*1)>0,
     INDEX(ResultCellRange,SUMPRODUCT(EXACT(SearchCellRange,SearchValue)*ROW(SearchCellRange))),
     ""
  )
)

---

Links

Tips. XLOOKUP & VLOOKUP & HLOOKUP(Microsoft Excel)

Tips. XLOOKUP & VLOOKUP & HLOOKUP(Microsoft Excel)

See more