VLOOKUP & INDEX + MATCH

Excel Tip #012

VLOOKUP & INDEX + MATCH

Excel has many formulas which functions differently. To analyze data, one of the most important formula is VLOOKUP and INDEX + MATCH. Let us now get into the details of it.

VLOOKUP:

Let us consider the below data as an example.
Sales Data
Let us say, we want to know how much sales did Jerald make? VLOOKUP can be used to answer this question.

What does VLOOKUP actually do?
     VLOOKUP searches a list for a value in left most column and returns corresponding value from adjacent columns.

Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
In simple terms,
= VLOOKUP(value/data that is used as reference, data table, the column # where our resultant data resides, exact (or) approximate match)

The final parameter is optional but very important. In the next tutorial, I will provide a few examples explaining how to correctly make formulas for exact (or) approximate match for VLOOKUP.

In our example of finding the sales of Jerald, we need VLOOKUP to search for Jerald (cell A6) and return the amount of sales he made from column 3.

The formula will be:
     =VLOOKUP(A6,$A$2:$C$11,3,FALSE)

VLOOKUP to get sales data for Jerald
To fetch the units sold by a person, the column # should be 2.

Some more examples:
Few more examples on VLOOKUP
You can notice that the 3rd question is left unanswered. This is because of a drawback of VLOOKUP, it cannot lookup values in the left.

To overcome this, we have to make use of the combination INDEX & MATCH in the next part.

INDEX + MATCH:

VLOOKUP can only return corresponding items from the column at right, not at left. One easy fix would be move the sales data to the left of sales rep. But this is an annoying and later you may want to lookup based on unit values too. So let us see can INDEX & MATCH can handle this scenario.

Below are the syntax of INDEX & MATCH.
     =INDEX(reference, row_num, [column_num])
     =MATCH(lookup_value, lookup_array, [match_type])

The basic syntax of this combination is like this
    =INDEX(column with data you want,MATCH(value you are looking for, column which contains this data,0)

Our problem is to find out the sales rep who made sales = $470. Sales rep names are in column A and the value $470 is in cell C8. The formula will be,

     =INDEX($A$2:$A$11,MATCH(C8,$C$2:$C$11,0))

INDEX + MATCH to get the sales rep name who made sales $470
Some more examples:

INDEX + MATCH can also work like VLOOKUP, meaning INDEX + MATCH combination is not only used to lookup values in the left but also towards right. Look at question 3 in the below example
Few more examples on INDEX + MATCH

Comments

Post a Comment

Popular posts from this blog

VLOOKUP - Exact Match & Approximate Match

Image
Excel Tip #013 VLOOKUP - Exact Match & Approximate Match In the previous tip #012 (VLOOKUP & INDEX + MATCH), we learnt about VLOOKUP, it's syntax and how to use it. We also learnt about its drawback and how INDEX & MATCH can be helpful. Like said in the previous tip, there are 2 match option available in VLOOKUP - Exact & Approximate Match . Let us first refresh the syntax of VLOOKUP. Syntax: = VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]) In simple terms, =  VLOOKUP (value/data that is used as reference, data table, the column # where our resultant data resides, exact (or) approximate match) Let's have a closer look at the last argument you supply to the Excel VLOOKUP function - range_lookup. You are likely to get different results in the same formula depending on whether you enter TRUE or FALSE (Approximate or Exact). If range_lookup is set to FALSE , the formula searches for exact match, i.e. for the lookup value exac
Read more

Row 1 Is Missing. How to unhide?

Image
Excel Tip #030 Row 1 Is Missing. How to unhide? As you know, hiding and unhiding rows in Excel is simple as selecting them, right-clicking and choosing Hide or Unhide . The trick to selecting hidden rows, is to select a row above or below and then drag to included the hidden rows. For example, if you accidentally hide row 1 of your worksheet or inherit someone else's worksheet with one or more of the top rows hidden, you may be wondering how to unhide them since apparently you cannot select them. Row 1 Hidden There is a really easy solution. 1) Point to the row 2 header , press the left mouse button and drag upward to the top of the worksheet. Although you cannot see it, row 2 and row 1 are now selected 2) Right click anywhere on row 2 3) Choose Unhide from the right-click menu Unhiding hidden Row 1 NOTE : This also works for multiple hidden rows. There is another method of doing the same if Row 1 is hidden. 1) Go to the top left corner of the sheet abov
Read more

Search Backwards in Excel

Excel Tip #022 Search Backwards in Excel Sometimes we take some things for granted in Excel and assume that everyone else knows too. Here's a tip which lot of Excel users don't know about it. Many Excel users know that the quickest way to find something in a large worksheet is to use Excel's Find command. On the Home tab, in the Editing group, click Find & Select , then Find or, preferrably, use the keyboard shortcut CTRL+F instead. Each time you click Find Next you will be taken to the next matching cell, starting from the active cell and going down the sheet until you finally come to the last one. But what if you want to find the 'last' matching value in a sheet and there are dozens or even hundreds of matching values? You don't want to click through each one until you finally reach the end. Here's a simple trick that will save you some time. Select a cell below the range you want to search ( CTRL+END is a shortcut you can use to quick
Read more