Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How To Remove Duplicates In Excel Column Or Row?
  • How to Create Gantt Chart in Excel
  • Get column name from index in Excel Table
  • Conditional Formatting Rules in Excel
  • How to add Trendline to a chart in Excel

References

  • Basic INDEX MATCH approximate in Excel
  • How to get first column number in range in Excel
  • Last row number in range
  • Offset in Excel
  • Merge tables with VLOOKUP in Excel

Data Validations

  • Excel Data validation must begin with
  • Data validation must not exist in list
  • Excel Data validation only dates between
  • Excel Data validation unique values only
  • Excel Data validation whole percentage only

Exact match lookup with INDEX and MATCH in Excel

by

This tutorial shows how to Exact match lookup with INDEX and MATCH in Excel using the example below;

Formula

{=INDEX(data,MATCH(TRUE,EXACT(val,lookup_col),0),col_num)}

Explanation

Case-sensitive lookup

By default, standard lookups with VLOOKUP or INDEX + MATCH aren’t case-sensitive. Both VLOOKUP and MATCH will simply return the first match, ignoring case.

However, if you need to do a case-sensitive lookup, you can do so with an array formula that uses INDEX, MATCH, and the EXACT function.

In the example, we are using the following formula

{=INDEX(data,MATCH(TRUE,EXACT(F4,B3:B102),0),3)}

This formula is an array formula and must be entered with Control + Shift + Enter.

How the formula works

Since MATCH alone isn’t case sensitive, we need a way to get Excel to compare case. The EXACT function is the perfect function for this, but the way we use it is a little unusual, because we need to compare one cell to a range of cells.

Working from the inside out, we have first:

EXACT(F4,B3:B102)

where F4 contains the lookup value, and B3:B102 is a reference to the lookup column (First names). Because we are giving EXACT an array as a second argument, we will get back an array of TRUE false values like this:

{FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,etc.}

This is the result of comparing the value in B4 every cell in the lookup column. Wherever we see TRUE, we know we have an exact match that respects case.

Now we need to get the position (i.e. row number) of the TRUE value in this array. For this, we can use MATCH, looking for TRUE and set in exact match mode:

MATCH(TRUE,EXACT(F4,B3:B102),0)

It’s important to note that MATCH will always return the first match if there are duplicates, so if there happens to be another exact match in the column, you’ll only match the first one.

Now we have a row number. Next, we just need to use INDEX to retrieve the value at the right row and column intersection. The column number in this case is hard-coded as 3, since the named range data includes all columns. The final formula is:

{=INDEX(data,MATCH(TRUE,EXACT(F4,B3:B102),0),3)}

We have to enter this formula as an array formula because of the array created by EXACT.

Post navigation

Previous Post:

COSH function: Description, Usage, Syntax, Examples and Explanation

Next Post:

Excel Data validation require unique number

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • Not Equal To ‘<>‘ operator in Excel
  • How to return blank in place of #DIV/0! error in Excel
  • IF function: Description, Usage, Syntax, Examples and Explanation
  • OR function Examples in Excel
  • SWITCH function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Add workdays to date custom weekends in Excel
  • List holidays between two dates in Excel
  • NETWORKDAYS.INTL function: Description, Usage, Syntax, Examples and Explanation
  • Add days exclude certain days of week in Excel
  • MONTH function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Group arbitrary text values in Excel
  • How to randomly assign people to groups in Excel
  • Group numbers at uneven intervals in Excel
  • Map inputs to arbitrary values in Excel
  • Group numbers with VLOOKUP in Excel

General

  • Flash Fill in Excel
  • Split Cell Content Using Text to Columns in Excel
  • Cell References: Relative, Absolute and Mixed Referencing Examples
  • List sheet names with formula in Excel
  • Subtotal by invoice number in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning