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:
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:
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.