Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to use Excel FORMULATEXT function
  • How to use Excel VLOOKUP Function
  • How to get relative column numbers in a range in Excel
  • Count rows with at least n matching values
  • Lookup entire row in Excel

Data Analysis Examples

  • Filter Data Based on Date in Excel
  • Subtotal function in Excel
  • How To Sort One Column or Multiple Columns in Excel
  • How to perform a t-Test in Excel
  • Get column index in Excel Table

Data Validation Examples

  • Excel Data validation date in next 30 days
  • Data validation must not exist in list
  • Excel Data validation allow uppercase only
  • Excel Data validation require unique number
  • Excel Data validation unique values only

Basic INDEX MATCH approximate in Excel

by

This tutorial shows how to calculate Basic INDEX MATCH approximate in Excel using the example below;

Formula

=INDEX(grades,MATCH(score,scores,1))

Explanation

This example shows how to use INDEX and MATCH to retrieve a grade from a table based a given score. This requires an “approximate match”, since it is unlikely that the actual score exists in the table. The formula in cell F5 is:

Worked Example:   Position of first partial match in Excel
=INDEX(C5:C9,MATCH(E5,B5:B9,1))

Which returns “B”, the correct grade for a score of 88.

How this formula works

This formula uses MATCH find the correct row for a given score.  MATCH is configured to look for the value in E5 in column B:

MATCH(E5,B5:B9,1)

Note that the last argument is 1 (equivalent to TRUE), which allows MATCH to perform an approximate match on values listed in ascending order. In this configuration, MATCH returns the position of the first value that is less than or equal to the lookup value. In this case, the score is 88, row 4 is returned.

Worked Example:   How to use Excel INDEX Function

Once MATCH returns 4 we have:

=INDEX(C5:C9,4)

Which causes INDEX to retrieve the value at the 4th row of the range C5:C9, which is “B”.

Worked Example:   How to calculate two-way lookup VLOOKUP in Excel Table

Note: values in column B must be sorted in ascending order in order for MATCH to return the correct position.

Post navigation

Previous Post:

Basic INDEX MATCH exact in Excel

Next Post:

Approximate match with multiple criteria 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
  • How to extract domain from email address in Excel
  • How to get first word in Excel
  • How to calculate Number of Instances in Excel
  • DOLLAR function: Description, Usage, Syntax, Examples and Explanation
  • How to Capitalize first letter in a sentence in Excel
  • Basic Overtime Calculation Formula in Excel
  • MINUTE function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate project start date based on end date in Excel
  • Add business days to date in Excel
  • How to calculate next scheduled event in Excel
  • DISC function: Description, Usage, Syntax, Examples and Explanation
  • Calculate periods for annuity in Excel
  • PPMT function: Description, Usage, Syntax, Examples and Explanation
  • XNPV function: Description, Usage, Syntax, Examples and Explanation
  • PRICE function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles