Excel allows us to lookup values with INDEX and MATCH functions. The MATCH function returns a row for a value in a table, while the INDEX returns a value for that row. If no value is found, we can avoid error using the IFERROR function. This step by step tutorial will assist all levels of Excel users to learn how to use the IFERROR, INDEX and MATCH functions together.
Figure 1. The final result of the formula
Syntax of the INDEX formula
=INDEX(array, row_num, column_num)
The parameters of the INDEX function are:
- array – a range of cells where we want to get a data
- row_num – a number of a row in the array for which we want to get a value
- column_num – a column in the array which returns a value.
Syntax of the MATCH formula
=MATCH(lookup_value, lookup_array, [match_type])
The parameters of the MATCH function are:
- lookup_value – a value which we want to find in the lookup_array
- lookup_array – the array where we want to find a value
- [match_type] – a type of match. We put 0 which is an exact match.
Syntax of the IFERROR formula
=IFERROR(value, value_if_error)
The parameters of the IFERROR function are:
- value – a value which we want to check if it is an error. In our case, it will be a result of the function
- Value_if_error – value which we want to return instead of an error.
Setting up Our Data for the Formula
Our table consists of 5 columns: “Delivery Number” (column B), “Product ID” (column C), “Delivery Date” (column D), “Amount” (column E) and “Product Description” (column F).
In column F, we want to populate a Product Description from the Lookup table in the range H3:I9. This table consists of 2 columns: “Product ID” (column H) and “Product Description” (column I). Two tables are joined by the columns “Product ID”.
Figure 2. Data that we will use in the example
Using the IFEEROR, INDEX and MATCH Formula
We want to get a product description in the cell F3, from the lookup table H3:I9, based on the Product ID 103 in the cell C3. If Product ID 103 is not found in the lookup table, we want to return “Description missing” in F3.
The formula looks like:
=IFERROR(INDEX($H$3:$I$9, MATCH(C3, $H$3:$H$9, 0), 2), “Description missing”)
The lookup_value parameter of the MATCH function is C3. The lookup_array is $H$3:$H$9, while the match_type is 0, as we want the exact match. The result of the MATCH function is the row_num parameter of the INDEX function. The array the range $H$3:$I$9. Finally, the value parameter of the IFERROR function is the result of the INDEX function, while the value_if_error is “Description missing”.
To apply the formula, we need to follow these steps:
- Select cell F3 and click on it
- Insert the formula:
=IFERROR(INDEX($H$3:$I$9, MATCH(C3, $H$3:$H$9, 0), 2), "Description missing") - Press enter
- Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.
Figure 3. Using the IFEEROR, INDEX and MATCH Formula
The MATCH function will return #N/A error, as Product ID 103 is not in the lookup table H3:I9. Therefore, the result of the whole INDEX function is the error. Because of that, the IFERROR function returns “Description missing” in the cell F3.
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment