Some examples of look up functions are Find and H/VLookup, but there are lots more listed in the category “Lookup & References” under Functions in Excel.
The example described is a combination function which we find easier and more reliable to use than the standard functions. Our Excel combined function consists of 2 different Excel functions nested together. But no worries they are easy to use and hopefully well explained bellow.
First, which functions do we use in this example:
- MATCH – This function is used to find the position (row number) of our search criteria in the dataset that we want to get a value from.
- INDEX – Returns the value in a specific row/column intersection based on the position (row number) found by use of the Match search and the column that contains the value we want to get.
Attached to this article you will find both an Excel sample sheet and a PDF of this text for printout. In the Excel sample file there is two sheets, one sheet is called dataset. This sheet contains a “100 books everyone should read” list and will be our library to find and get data from.
The second sheet in the workbook is called LookUpValues, in this sheet you will find all sample codes used in this article with some more examples including explanation text. Fields with functions in has a soft orange background colour.
In our example we use a book title “Harry Potter and the Sorcerer's Stone” to find the ISBN number that belongs to the book.
How to look up a value in 3 steps:
1. Use of the Match Function
Match is the function that looks for a given value in another Sheet/Column and returns the row number.
The first code to use, the inner one in our nested example is Match. This is the code in the LookUpValues sheet that gives 6 in field C2. It is shown in the picture to the right and with an example in the attached Excel workbook. The code used is “=MATCH(B2; Dataset!B:B; 0)”.
- “Match” is the function and all criteria’s for the function is separated by semicolon and found inside the brackets.
- Criteria 1, “B2” is the Row/Column intersection that contains the data-record to search for.
- Criteria 2, “Dataset!B:B” in the Match function is the part that routes the search to the complete column B (B:B equals complete column) in the “Dataset” sheet (Dataset! routes the search to the Dataset sheet).
- Criteria 3, “0” in the Match function is an instruction for Match to only find exact matches of Criteria 1, which is the contents of field “B2”.
So, in this case is Match returning number 6, which is the row in the sheet “Dataset” where column B contains the book title “Harry Potter and the Sorcerer's Stone”.
(Open the Excel sheet, select everything in the Dataset sheet and try to re-order the contents. When doing that you will see that the value in C2 is changing based on which row the book “Harry Potter and the Sorcerer's Stone” is found.
2. Use of the Index Function
Index is the function that uses a Column/Row coordinate to look up and return a value.
In Column C and Row 3 of our LookUpValues sheet we use the Index function to find the ISBN number from the Dataset sheet.
Here we use the value (row number) we found in section 1, Explanation for use of Match Function. The Code in C3 shown in the picture to the right and in the attached Excel workbook is “=INDEX(Dataset!C:C;LookUpValues!C2)”.
- INDEX is the function and all criteria’s for the function is separated by semicolon and found inside the brackets.
- Criteria 1, “Dataset!C:C” instructs the Index function that we only want a record from Column C in the Dataset sheet.
- Criteria 2, LookUpValues!C2 instructs the Index function that we only want contents from the row we found under section 1, the row found was 6.
So, in this case does Index returns ISBN number “978-0545790352” for the book titled “Harry Potter and the Sorcerer's Stone”. This is the content of intersection row 6/column C in the sheet “Dataset”.
3. How to combine Index and Match
The last part is to combine Index and Match. It is straight forward to test this, just replace second criteria “LookUpValues!C2” of the INDEX function with a copy of the complete MATCH function except the equal mark “MATCH(B2;Dataset!B:B;0)”.
This should give the following code: =INDEX(Dataset!C:C; MATCH(B2;Dataset!B:B;0) )
By that we have made our own search function! The use of complete columns instead of a range of rows inside a column makes the function much easier to move, expand, copy and so on.
Code line converted into plain text:
The short story for our look up function, this example is found in row 4 in the excel sheet “=INDEX(Dataset!C:C;MATCH(B4;Dataset!B:B;0))” and is described in plain text below.
Get us the value we’re looking for:
Return the Value we’re looking for from this sheet and column:
Get me the row number where the value we’re looking for is found:
Use the value in this row and column intersection as the criteria:
Search after the criteria value in this sheet and column:
Get us only a result when search criteria match perfectly:
This became a little more complex to explain than we foreseen, so a smaller example will follow using one sheet and just some few records. Should make it easier to understand the basics.
But still hope you enjoyed this one and is ready for exploring Index and Match…..