How ToUse INDEX & MATCH With IFERROR in Excel

Let’s take a look at using the INDEX and MATCH function combination and wrapping it in the IFERROR function to keep it clean from error messages:

(Note: Video version above, text version below)

Text version (note: click on an image for a better view):

Here we have a data range with aa to ff as the rows and jan to may as the columns. I’ve set up a area to enter the row and column reference in F10:G11 and I want the result of the intersection noted in G11:

iferror1

In cell G13 I’ve entered the INDEX function, hardcoding the row and column references to show the results of the intersection of row 2 and column 2 in the data range G3:K8. The syntax for INDEX is =INDEX(array,row_number,[column_number]).

Now, instead of hardcoding the row and column numbers, we can use the MATCH function to indicate those. The syntax for MATCH is =MATCH(look_up_value,look_up_array,[match_type]). I’m using “0” as the match_type to indicate an exact match. In rows 14 & 15 I’ve shows the results of the row for “bb” and the column for “feb”, which both happen to be row 2 and column 2. That intersection is 1000.

Now, if we combine the INDEX and MATCH functions together, we get the following formula: =INDEX($G$3:$K$8,MATCH(F$10,F$3:F$8,0),MATCH(G$10,G$2:K$2,0))

In this way, if we change the references in cells F10 or G10, it will give us the result of that intersection.

However, if we enter an invalid entry, we get an #N/A error message:

iferror2

But notice the alternate entry in cell G19. Here is where I wrapped the IFERROR function around the INDEX/MATCH functions to eliminate the #N/A error and replaced it with a message “Try Again!”. I could have also just used double quotes “” and it would have just inserted a blank. Either way, using the IFERROR function makes our spreadsheet look much cleaner if errors are made.

The IFERROR syntax is =IFERROR(value,value_if_error). In our case, the “value” would be the result of the INDEX/MATCH functions if there is no error, and the “value_if_error” would be “Try Again!” if there was an error.

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , ,

6 Responses to How ToUse INDEX & MATCH With IFERROR in Excel

  1. Gopinath March 24, 2016 at 2:23 am #

    Thank you so much sir, I didn’t knew this simple method of doing, and i prepared a format with if and lookup in a much time consuming and hard r. I really appreciate your practice to teach people with these simple yet brilliant methods. Again Thank you.

  2. Pens October 24, 2016 at 2:29 am #

    hi sir if i may ask, if lets say i have a lot of sheets, is there any way i can get around with index match and more if error?.

    i have tried using iferror(index(match));iferror, but to no avail it didnt show up the way i wanted to be, is there any way i can get around with this? thanks in advance

  3. Dianne April 19, 2017 at 2:16 pm #

    Thanks Michael for this post. Wow! came in very handy.

Leave a Reply