**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 how 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.

**Table of Contents**hide

## Download Practice Workbook

You can download the practice workbook from the link below.

## 4 Ways to Map Data Using VLOOKUP in Excel

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 **the 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 **ID**s, 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** is 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 have data in **multiple worksheets** 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 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**#NA**, 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.

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