Index and Match – Part 2

 

Today I am going to show you how easy it is to get information from various columns and rows. This will also show you a custom error that you can use.

Have a look at the formula below. It looks complicated, but take it step by step.

=IFERROR(INDEX(C3:E10,MATCH(C12,B3:B10,0),MATCH(C13,C2:E2,0)),"No name or month entered.")

Here I have a lost of names and three months with amounts in them. Let’s say that they are sales, but they can be anything. You would like to know what the amount is for a certain person for a specific month.

excelguy.co.za

  • The magic all occurs in cell D13. Let’s break this down a bit to make it easier to understand.=IFERROR(INDEX(C3:E10,MATCH(C12,B3:B10,0),MATCH(C13,C2:E2,0)),”No name or month entered.”)
  • =IFERROR is used to detect if data is not entered or incirrect data.
  • (INDEX(C3:E10,MATCH(C12,B3:B10,0), The C3:E10 are the range of cells containing the numbers (Sales). Now you MATCH what you entered into C12 (Name of the person). The range B3:B10 is where the name is stored. The 0 means absolute value.
  • MATCH(C13,C2:E2,0)),”No name or month entered.”) Here is the second match (for the Month). The month is entered in cell C13 and it is contained in the range of cells C2:E2 with an absolute value. The No Name or month entered is a custom error message as can be seen below

excelguy.co.za 

As you can see. If you type in a name of a person and/or month that is not stored, then you will get an error. One point to remember is that the INDEX MATCH here is not case sensitive, so you can use lower case, upper case or a mixture. The results will be the same.

This looks complicated at first, so why not create a small spreadsheet with some data and practice this. If you need any help, feel free to get in touch.