OmniDoc# Core comparison
Explain clearly the division of labor and writing methods of INDEX and MATCH combinations, learn to find names by job number, search to the left, and avoid common errors such as changes in column order and inconsistent text and number formats.
#How to find data using Excel INDEX and MATCH combination
Key points of this article
- MATCH is responsible for finding the row number where the search value is located, and INDEX takes out the content in the return area based on the row number. The division of labor between the two is clear.
- Compared with VLOOKUP, INDEX plus MATCH can search to the left and does not rely on the position of the returned column, making it more stable when inserting or deleting columns.
- The starting and ending lines of the two ranges must be consistent. Write 0 in the third parameter of MATCH to achieve an exact match.
- When #N/A appears, first check for spaces, text and number formats, and whether the lookup value actually exists in the lookup column.
In Excel, it is a very common table lookup requirement to find the name by job number and the price by product number. The first thing many people think of is VLOOKUP, but it can only search from left to right, and usually requires a column number. Once the table structure changes, it is easy to make errors. The combination of INDEX and MATCH is a more flexible way of writing Excel search functions. It can search to the left and does not depend on the order of columns.
This article is written for readers who already know how to use the basic formulas, but have not yet mastered this combination. After reading, you can understand what INDEX and MATCH are responsible for, write your own search formula, and check whether the results are reliable.
How to choose INDEX and MATCH when checking data? One sentence conclusion
Whenever the table structure may change, or you need to find out the value on the left based on the column on the right, use the combination of INDEX and MATCH. It first uses MATCH to find the location, and then uses INDEX to get the data.
| your situation | Recommended practices | reason |
|---|---|---|
| The search column is to the left of the returned column, and the table will not be changed in the future. | VLOOKUP or INDEX plus MATCH can be used | The result is the same, VLOOKUP is shorter. |
| Need to find the value on the left based on the column on the right | INDEX plus MATCH | VLOOKUP cannot look directly to the left |
| Columns may be inserted or deleted later | INDEX plus MATCH | VLOOKUP with hard-coded column numbers will cause errors due to column changes. MATCH will locate based on the actual area. |
| Two conditions need to be met at the same time to find out | Multi-condition writing method of INDEX plus MATCH | It is difficult for a single VLOOKUP to satisfy both conditions at the same time |
Before you start: Confirm these points first
- Find if a value is duplicate. MATCH returns the first match. If the same job number appears twice in the table, the formula will only return the first record, making it easy to miss duplicate data.
- Whether the number of rows in the two ranges is consistent. The return area of INDEX and the search area of MATCH must be the same length, for example, both line 2 to line 300. Inconsistent row numbers may result in misaligned results or errors.
- Whether the formats of numbers and text are consistent. If the employee number column is a text number "1001" and the search value you enter is the number 1001, MATCH will not find it. Such problems are difficult to see on the surface and require special inspection.
- Whether an exact match is required. This article talks about precise search. The third parameter of MATCH should be written as 0. Approximate lookups (such as segmented tax rates) are used differently and are outside the scope of this article.
- Whether the data area will increase. If rows will be added to the table in the future, consider whether the range can cover the new data, otherwise the new records will not be found.
Steps to use INDEX and MATCH to check names by job number
The two functions have a clear division of labor: MATCH returns the position (row) of the search value in a certain column, and INDEX takes out the contents of the corresponding cell in another column based on this position. Let's take an employee table as an example: Column A is the employee number, column B is the name, column C is the department, and the data starts from row 2.
- Confirm that the header is in row 1, the data starts in row 2, the job number is in column A, and the name is in column B.
- Enter the job number to be checked in cell G2, for example 1003. Put the search value in a separate cell, and only change this part of the au pair number in the future.
- First write INDEX in cell H2. The first parameter is the return area, which is the name column B2:B300.
- Then write MATCH. The first parameter is the search value G2, the second parameter is the search area A2:A300 (employee number column), and the third parameter is written 0 to indicate an exact match.
- The complete formula is: =INDEX(B2:B300,MATCH(G2,A2:A300,0)). After pressing Enter, H2 displays the name corresponding to job number 1003.
- Check whether the starting and ending lines of the two areas are consistent. In this example, they are both lines 2 to 300.
- If you want to fill the formula downward, first change the area to an absolute reference, such as $B$2:$B$300 and $A$2:$A$300, to prevent the area from moving when dragging.
- Modify the job number in G2, and H2 will automatically update the result.
The search to the left is written in the same way, except that the two areas are reversed. For example, if you want to check the job number based on the name, the formula is: =INDEX(A2:A300,MATCH(G3,B2:B300,0)). This is exactly what VLOOKUP cannot do.
Check each item after completion
After the formula is written, do not trust the first result directly. It is recommended to check item by item according to the table below.
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| Is the result correct? | Randomly select two or three job numbers and manually compare the names in the original table | Check whether the starting and ending rows of the two areas are consistent and whether the area is selected in the wrong column. |
| Does #N/A appear? | See if the cell displays # Ngamma A | Check whether there are spaces before and after the lookup value, and unify the text and number format |
| Find out if the value is duplicated | Use COUNTIF to find the number of times this value appears in the column | When there is a repetition, confirm which item you want first, and use multi-condition search if necessary. |
| Whether the filled area is out of line. | After filling downward, click on a few formulas to see if the area is still the original range. | Add $absolute reference to the area |
| Did you cover up the mistake? | If IFERROR is applied, see if it shows all errors as blank | Remove the IFERROR check first, confirm it correctly, and then add it back. |
| Comparison term | VLOOKUP | INDEX plus MATCH |
|---|---|---|
| Search direction | Can only be checked from left to right | It can be left or right. |
| Returns the positioning of the column | Specify with column serial number, write dead with column serial number | Locate by return area, column increase or decrease has less impact. |
| Insert or delete columns | It is easy to make errors due to changes in column serial numbers. | As long as the area is correct, it is usually not affected. |
| Writing method | Shorter, faster to use. | A little longer, need to understand the two-step division of labor |
| Multi-condition search | More tedious | Can be implemented in array writing. |
# frequently asked questions (FAQ)
1. Can INDEX and MATCH do multi-condition search?
Sure. The common way to write is to concatenate two conditions with a multiplicative sign, and then put them into MATCH to find a value to write 1, for example: = INDEX (D2A2:A300=G2 D300 match (1, (A2:A300=G2) * (C2:C300=G3), 0)). The old version of Excel may need to be confirmed by Ctrl+Shift+Enter, but the new version can usually be entered directly.
2. What does it mean when the formula says # NCMA?
Indicates that MATCH did not find a lookup value in the lookup column. First check whether the work number really exists, and then check if there are any extra spaces, and whether the text is consistent with the numeric format.
3. What if the cell found is blank and the result shows 0?
If the INDEX takes out a blank cell, the result will be displayed as 0. You can judge with IF on the outer layer, and the value taken out will be displayed as blank when it is empty, but it should be noted that doing so will make it more difficult to find the null value. It is recommended to make sure that the data itself is correct before using it.
4. Can other lookup functions of the new version of Excel replace it?
Newer versions of Excel provide additional lookup functions, but INDEX and MATCH are compatible with all common versions. If forms are to be shared between different versions of computers, it is safer to use INDEX plus MATCH.
# Summary
Remember a division of labor: MATCH is responsible for finding locations and INDEX is responsible for fetching content. As long as the two ranges have the same number of rows, the same lookup value, and the exact matching parameter 0, this Excel lookup combination can handle most scenarios where left lookups and column positions change.
