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

Data Analysis

  • How To Sort One Column or Multiple Columns in Excel
  • How to Create Area Chart in Excel
  • Number and Text Filters Examples in Excel
  • How to Use Solver Tool in Excel
  • How To Create Pareto Chart in Excel

References

  • Offset in Excel
  • CHOOSE function: Description, Usage, Syntax, Examples and Explanation
  • How to get address of named range in Excel
  • Left Lookup in Excel
  • Count unique text values with criteria

Data Validations

  • Excel Data validation don’t exceed total
  • How To Create Drop-down List in Excel
  • Excel Data validation require unique number
  • Excel Data validation allow weekday only
  • Excel Data validation date in next 30 days

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

Next Post:

Win loss points calculation in Excel

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

  • IFERROR function: Description, Usage, Syntax, Examples and Explanation
  • IF with boolean logic in Excel
  • How to use Excel XOR Function
  • Extract multiple matches into separate rows in Excel
  • How to use IFS function in Excel

Date Time

  • How to calculate Day of the Year in Excel
  • Convert decimal seconds to Excel time
  • Steps to create Dynamic calendar grid in Excel
  • Count dates in current month in Excel
  • Get date from day number in Excel

Grouping

  • How to randomly assign data to groups in Excel
  • Calculate conditional mode with criteria in Excel
  • Categorize text with keywords in Excel
  • Map text to numbers in Excel
  • Group numbers at uneven intervals in Excel

General

  • How to set or clear a print area in Excel Worksheet
  • How to get original price from percentage discount in Excel
  • 231 Keyboard Shortcut Keys In Excel
  • Basic text sort formula in Excel
  • Find Most Frequently Occurring Word in Excel Worksheet
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning