1 look-up function, Index and Match

In today’s article the focus are the lookup functions Index and Match, and how they can be used together. Attached to the Article you will find a PDF version for printout. There is also an Excel file that have one sheet containing a dataset (booklist with columns author, title and ISBN) and a task part containing 4 columns; Task, Search Criteria, Your Test and Answer.

You can test yourself by entering the code described further down in this article into column C, “Your Test” in the attached Excel sheet. In Column D, answer you will see what result you’re supposed to get.

Index and Match, what are they and what do they do:

  • MATCH – This function is used to find the position (row number) of a given search criteria in the dataset we want to get the value from. The purpose of Match is by that to find the row/column coordinate for the value the user is looking for.
  • INDEX – Returns the contents in a specific row/column intersection based on a position given in its criteria’s (Column and row number). The purpose of Index is by that to return contents of a field elsewhere in the workbook to the field where the user wants it.

When combining these two functions is Match used to find the row number for a certain search criteria and Index to return a value from the same row but different column.

Please use attached Excel sheet for practical training on these functions, snapshot of it at the bottom of the article for field references.

How to use Match

  1. Start by placing the marker in C13. Type in “=Match(“
  2. Select field B13 either by use of the mouse
  3. Enter a semicolon “;”
  4. Select the data range to look in, “B1:B10” by use of the mouse
  5. Enter a semicolon “;”
  6. Type 0 for “Exact Match”
  7. End code with a “)” and press enter
  8. The shown result in C13 should be “3”

How to use Index

  1. Start by placing the marker in C14. Type in “=Index(“
  2. Select the data range to look in, “A1:A10” by using the arrow keys to move to A1, press and hold shift and then move to A10 with the arrow keys.
  3. Enter a semicolon “;”
  4. Select field B14 by using the arrow keys to move to B14
  5. End code with a “)” and press enter
  6. The shown result in C14 should be “Harry Potter and the Sorcerer's Stone”

Tip: Compare your code with the code under Answer ff Excel gives an error to find any mistakes.

How to use Index and Match together

  1. Start by placing the marker in C15. Type in “=Index(“
  2. Select the data range to look in, “C1:C10”
  3. Type in a semicolon “;” followed by “Match(“
  4. Select field B15 or write B15.
  5. Enter a semicolon “;”
  6. Select field “A1:A10”
  7. Enter a semicolon “;” and “0” for exact match followed by “))”.
  8. Press Enter, given result in C15 should then be 978-0743273565

Row 16 to 20 in the attached Excel spreadsheet gives more exercises with Index and Match used together. The only difference from above sample is the search criteria, where we search and what to return. Quite simple, so please feel free to play around with it.

Please follow and like us:
error
RSS
Follow by Email
Facebook
Twitter
LinkedIn