Скачать или смотреть Google Sheets | INDEX-MATCH | Function | Drawback | Alternative | Example | Spreadsheet | Tutorial

In Google Sheets, the INDEX-MATCH combo extracts data from the cell at the intersection of the specified row and column for the specified keyword. However, you have to use two functions, and nest MATCH twice inside the INDEX function. A powerful alternative would be to use XLOOKUP.

XLOOKUP can do a horizontal, vertical, or both lookup, making it a strong substitute for the INDEX-MATCH combo.

For more info on XLOOKUP, please review the tutorial given below.

*************************************
How to Use INDEX in Google Sheets?

INDEX can extract data from a cell at the intersection of specified row and
column:

   • Google Sheets INDEX | Use INDEX Google She...  
*************************************

*************************************
How to Use MATCH in Google Sheets?

MATCH returns the position of the search key in the specified column:

   • Google Sheets Match | Match Function Googl...  
*************************************

-------------------------------------
How to Use VLOOKUP in Google Sheets?

Use VLOOKUP to extract a single value:

   • VLOOKUP Google Sheets | How to Use VLOOKUP...  
-------------------------------------
How to Create a Pivot Table in Google Sheets?

Create a pivot table for calculation and in-depth data analysis:

   • Google Sheets | Pivot Table | How to Creat...  
-------------------------------------
How to Use Convert to Table in Google Sheets?

Convert a data range into a table easily:

   • Google Sheets | Convert to Table | Change ...  
-------------------------------------
How to Use SCAN in Google Sheets?

It's easy to calculate running total, running count and the like with SCAN:

   • Google Sheets | SCAN | Function | Calculat...  
-------------------------------------
How to Use FREQUENCY in Google Sheets?

FREQUENCY returns the frequency distribution of data:

   • Google Sheets | FREQUENCY | Function | Cou...  
-------------------------------------
How to Use COUNTIFS in Google Sheets?

Count values that fulfill one or more criteria:

   • How to use COUNTIFS in Google Sheets | COU...  
-------------------------------------

1. INDEX-MATCH

The INDEX-MATCH combo function to extract a value from a cell, corresponding to a specified search key, is:

=INDEX(reference, MATCH(search_key, range, [search_type]))

reference is the range from which to extract data.

search_key is the value to search.

range is the range in which to search for the search key.

search_type is optional. A value of 0 (default) is for an exact match.

Here is an example of the INDEX-MATCH combo function :

=INDEX(B3:B8, MATCH(G4,A3:A8,0))

MATCH will return the row number of the search key, G4, searching for it in A3:A8. INDEX will return the value in the cell at the intersection of row number and column, which is B3:B8.

2. XLOOKUP

XLOOKUP can do the above task by itself. That is, it does not require any
additional function.

The syntax of XLOOKUP function formula to extract a value from a single cell is:

=XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])

An example of XLOOKUP function formula for the same task as above:

=XLOOKUP(G4,A3:A8,B3:B8)

XLOOKUP will search for the search key, G4, in the range A3:A8, and return the corresponding value in the column B3:B8.

Review this video tutorial, which gives the steps to use the Google Sheets INDEX-MATCH combo function, and XLOOKUP to extract a value from a single cell, and tells why XLOOKUP could be a powerful alternative.