Mapping Data is an essential part of Excel. Therefore, knowing a couple of handy methods to map data can save a lot of time and improve the workflow. With this in mind, this article demonstrates 4 useful ways to map data in Excel **VLOOKUP**. Furthermore, we’ll also discuss how to obtain the Nth occurrence of a value and hide errors using the **VLOOKUP** function.

## Map Data Using VLOOKUP in Excel: 4 Ways

In this article, we’ll combine the **VLOOKUP** function with **MATCH**, **COUNTIF**, **INDIRECT**, and **IF functions** to map data. So, without further delay, let’s dive in!

Here, we have used *Microsoft Excel 365* version. You may use any other version according to your convenience.

__Method-1__: Using VLOOKUP Function to Map Data in Excel

Let’s start things off with the most obvious method i.e. using **VLOOKUP** function to map data in Excel. So, let’s begin.

Considering the dataset shown in the **B4:D14** cells. Here, the dataset shows a list of employee IDs, their Names, and the Department where they work.

** Steps**:

- At the very beginning, navigate to the
**G5**cell and enter the expression below.

`=VLOOKUP(G4,B5:D14,3,FALSE)`

In this formula, the **G4** cell refers to the **ID** *1008, *and the **B5:D14** range of the cells represents the *ID*, *Name*, and *Department* columns.

**Formula Breakdown:**

**VLOOKUP(G4,B5:D14,3,FALSE) →**looks for a value in the left-most column of a table and then returns a value in the same row from a column you specify. Here,**G4**(argument) is mapped from the*lookup_value***B5:D14**(argument) array. Next,*table_array***3**(argument) represents the column number of the lookup value. Lastly,*col_index_num***FALSE**(argument) refers to the*range_lookup***Exact match**of the lookup value.**Output → Marketing**

Finally, the results should look like the image given below.

__Method-2__: Mapping Data with VLOOKUP and MATCH Functions (Two-way VLOOKUP)

In our second method, we’ll combine the **VLOOKUP** and **MATCH** functions to map a value at the intersection of a particular row and column. This is also known as *Two-way VLOOKUP*. So, let’s see it in action.

Assuming we have the Sales List dataset shown in the **B4:E12 **cells. Here, we have the list of Items and the number of Units Sold in January, February, and March.

** Steps**:

- Initially, select the Item and the Month, for instance, we’ve chosen Television and March respectively.
- Next, go to the
**H6**cell and enter the formula given below.

`=VLOOKUP(H4, B6:E10, MATCH(H5, B5:E5, 0), FALSE)`

Here, the **H4** and **H5** cells refer to the Item and Month respectively while **B5:E5 **represents the column headers.

**Formula Breakdown:**

**MATCH(H5, B5:E5, 0) →**returns the relative position of an item in an array matching the given value. Here,**H5**is theargument, which refers to the*lookup_value**Month of March*. Following,**B5:E5**represents theargument from where the value is matched. Lately,*lookup_array***0**is the optionalargument, which indicates the*match_type***Exact match**criteria.**Output → 4**

**VLOOKUP(H4, B6:E10, MATCH(H5, B5:E5, 0), FALSE) →**becomes**VLOOKUP(H4, B6:E10, 4, FALSE) →**Here,**H4**(argument) is mapped from the*lookup_value***B6:E10**(argument) array. Next,*table_array***4**(argument) represents the column number of the lookup value. Lastly,*col_index_num***FALSE**(argument) refers to the*range_lookup***Exact match**of the lookup value.**Output → 243**

Lastly, your result should look like the picture given below.

__Method-3__: Utilizing VLOOKUP and COUNTIF Functions to Map Data

Another way to map data in Excel is to incorporate the **COUNIF **function within the **VLOOKUP** function. It’s simple and easy. Just follow these steps.

Let’s consider the Best Selling Books dataset shown in the **B4:C11 **cells. In this dataset, we have the names of the Best Seller and its Price in USD respectively.

** Steps**:

- To begin, move to the
**F5**cell and enter the expression below.

`=IF(COUNTIF(B5:B9,F4),VLOOKUP(F4,B5:C9,2,TRUE),0)`

In this expression, the **B5:C9** range of cells represents the Best Seller Book and the Price columns. In contrast, the **F4** cell refers to the Best Seller (here, it is *House of Wisdom*)

**Formula Breakdown:**

**COUNTIF(B5:B9,F4) →**counts the number of cells within a range that meet the given condition. Here,**B5:B9**is theargument that refers to the*range**Best Seller Books*. Following,**F4**represents theargument, which returns the count of the matched value.*criteria***Output → 1**

**VLOOKUP(F4,B5:C9,2,TRUE) →**Here,**F4**(argument) is mapped from the*lookup_value***B5:C9**(argument) array. Next,*table_array***2**(argument) represents the column number of the lookup value. Lastly,*col_index_num***TRUE**(argument) refers to the*range_lookup***Approximate match**of the lookup value.**Output → 25**

**IF(COUNTIF(B5:B9,F4),VLOOKUP(F4,B5:C9,2,TRUE),0) →**becomes**IF(1,25,0) →**checks whether a condition is met and returns one value if**TRUE**and another value if**FALSE**. Here,**1**is theargument which prompts the*logical_test***IF**function to return**25**(argument) otherwise it returns*value_if_true***0**(argument).*value_if_false***Output → $25**

Consequently, the results should look like the screenshot shown below.

__Method-4__: Mapping Data Using VLOOKUP and INDIRECT Functions in Excel

You can also map data in Excel by combining the **INDIRECT** and **VLOOKUP** functions. Now, let’s go through the steps.

Let’s say we have the Grocery List dataset shown in the **B4:I10** cells. The dataset shows the Price of the same Items in 3 cities across the *US*.

** Steps**:

- Firstly, go to the
**B5**cell >> click the**Data**tab >> then press the**Data Validation**drop-down.

This opens the **Data Validation** dialog box.

- Secondly, in the
**Allow**drop-down, choose the**List**option >> in the**Source**field, select the**B6:B10**cells >> click the**OK**button.

- Thirdly, select the
**B6:C10**cells >> go to the**Formulas**tab >> double-click the**Define Name**option.

This opens the **Edit Name** wizard.

- Here, enter a suitable name (in this case,
*Boston*) for the data range >> click the**OK**button.

In a similar fashion, define the **Named Range** for *Atlanta*.

Likewise, define the **Named Range** for *Denver*.

- Fourthly, navigate to the
**C14**cell >> on the**Data tab**, and click the**Data Validation**button.

- Similarly, choose the
**List**option >> enter the**Named Ranges**as shown in the screenshot below.

Now, choose the **Item **and the **Location **from the drop-down. For instance, we’ve chosen *Tomato* and *Atlanta *respectively.

- Afterward, in the
**D14**cell type in the following expression.

`=VLOOKUP(B14,INDIRECT(C14),2,FALSE)`

Here, the **B14** and **C14** cells point to the **Item** and **Location** respectively.

**Formula Breakdown:**

**INDIRECT(C14) →**returns the reference specified by a text string. Here,**C14**is theargument that refers to the*ref_text***Named Range***Boston*.**VLOOKUP(B14,INDIRECT(C14),2,FALSE) →**Here,**B14**(argument) is mapped from the*lookup_value***Named Range,****INDIRECT(C14)**that is theargument. Next,*table_array***2**(argument) represents the column number of the lookup value. Lastly,*col_index_num***FALSE**(argument) refers to the*range_lookup***Exact match**of the lookup value.**Output → $1.2**

Subsequently, after completing the above steps the results should look like the picture given below.

Moreover, you may also use this method in case you want to map data from another sheet, and you want to map it from specific worksheets based on the lookup value.

## Applying VLOOKUP Function to Obtain Nth Occurrence

Suppose you have the Stationery Sales list as shown in the **B4:D13** cells below. Now, you want to know the 2nd or the 3rd item purchased by the same customer. In order to solve this issue, we can employ the **COUNTIF** and the **VLOOKUP **functions to obtain the results. So, let’s see the process in detail.

** Steps**:

- Firstly, insert a column in
**Column B**and rename its header to*Helper Column*>> in the**B5**cell and enter the formula given below.

`=C5&COUNTIF($C$5:C5, C5)`

**📄 Note:** Please make sure to insert the Helper Column at the left-most side of the dataset, since by default the **VLOOKUP** function looks from left to right.

Here, the **C5** cell refers to the Name *John,* and the **COUNTIF** function counts the occurrence of *John* from the given range **$C$5:C5**. Finally, the **Ampersand (&) **operator combines the text and the number.

- Next, enter the Name and the Instance, for example, here it is
*Julie*, and*3*>> go to the**H6**cell and type in the expression below.

`=VLOOKUP(H4&H5, B5:E13, 3, FALSE)`

In this formula, the **H4** and **H5** cells indicate the Name and the Instance.

After completing the above steps, the outcome should look like the screenshot shown below.

## Using VLOOKUP and IF Functions to Hide #N/A Error

You can employ the **ISNA** and the **VLOOKUP** functions in the** IF **function to hide **#N/A** errors when an invalid lookup value is given. Now, allow me to demonstrate the process below.

** Steps**:

- At the very beginning, navigate to the
**G5**cell and enter the expression below.

`=IF(ISNA(VLOOKUP(G4, B5:D14,3,FALSE)), "",VLOOKUP(G4, B5:D14,3,FALSE))`

In this formula, the **G4** cell refers to the **ID** *1008 *and the **B5:D14** range of the cells represents the *ID*, *Name*, and *Department* columns.

**Formula Breakdown:**

**ISNA(VLOOKUP(G4, B5:D14,3,FALSE)) →**check whether a value is**#N/A**, and returns**TRUE**or**FALSE**. Here,**G4**(argument) is mapped from the*lookup_value***B5:D14**(argument) array. Next,*table_array***3**(argument) represents the column number of the lookup value. Lastly,*col_index_num***FALSE**(argument) refers to the*range_lookup***Exact match**of the lookup value.**Output → FALSE**

**IF(ISNA(VLOOKUP(G4, B5:D14,3,FALSE)), “”,VLOOKUP(G4, B5:D14,3,FALSE)) →**becomes**IF(FALSE, “”,VLOOKUP(G4, B5:D14,3,FALSE)) →**checks whether a condition is met and returns one value if**TRUE**and another value if. Here,**FALSE**is theargument which prompts the*logical_test***IF**function to return the value from**VLOOKUP**function (argument) otherwise it returns blank*value_if_true***“”**(argument).*value_if_false***Output → HR**

Eventually, you should get the results shown in the picture below.

In addition to this, if we enter an invalid ID number (*ID 1012*), the formula returns a blank value instead of the **#N/A** error which is shown in the screenshot given below.

## Practice Section

We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

**Download Practice Workbook**

You can download the practice workbook from the link below.

## Conclusion

I hope all the methods on how to map data in Excel using **VLOOKUP** will now prompt you to apply them in your Excel spreadsheets more effectively. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.

## Related Articles

**<< Go Back To Data Mapping in Excel | Importing Data in Excel | Learn Excel**