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.

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

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)”.

Explanation:

• “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.

Click here to learn how to use Microsoft Excel shortcuts

2. Look up a value with use of the Index Function

Belowe is the second explanation out of three ways to look up a value in Excel.

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)”.

Explanation:

• 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 to look up a value

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 lines 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		INDEX(
Return the Value we’re looking for from this sheet and column:		Dataset!C:C;
Get me the row number where the value we’re looking for is found:		MATCH(
Use the value in this row and column intersection as the criteria:		B4;
Search after the criteria value in this sheet and column:		Dataset!B:B;
Get us only a result when search criteria match perfectly:		0))

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

---

Now it is your turn to try it! Download our file under the Excel icon above and try it out.

More tips and tricks on Office-Tips.net….

Move entire rows & columns in Microsoft Excel

Click here to learn a smooth way to drag and drop rows and columns to a new position.

---

Unhide everything or parts of the data in a spreadsheet.

Click here to learn how to unhide everything or just part of the hidden data in a Microsoft Excel spreadsheet.

---

Office-Tips.net article overview

Do you wish to read more of our articles about office products? To get a full overview of all Office-Tips.net articles it is just to [click here].

If user guidance to Excel is the topics you are looking for. Then it is just to [Click here]. This page shows an overview of all Microsoft Excel related articles.