How to Map Data Using VLOOKUP in Excel (4 Quick Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

Dataset 1

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 ( lookup_value argument) is mapped from the B5:D14 (table_array argument) array. Next, 3 (col_index_num argument) represents the column number of the lookup value. Lastly, FALSE (range_lookup argument) refers to the Exact match of the lookup value.
    • Output → Marketing

Using VLOOKUP Function

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

How to Map Data in Excel Vlookup Using VLOOKUP Function


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.

Dataset 2

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 the lookup_value argument, which refers to the Month of March. Following, B5:E5 represents the lookup_array argument from where the value is matched. Lately, 0 is the optional match_type argument, which indicates the Exact match criteria.
    • Output → 4
  • VLOOKUP(H4, B6:E10, MATCH(H5, B5:E5, 0), FALSE) → becomes
    • VLOOKUP(H4, B6:E10, 4, FALSE) → Here, H4 ( lookup_value argument) is mapped from the B6:E10 (table_array argument) array. Next, 4 (col_index_num argument) represents the column number of the lookup value. Lastly, FALSE (range_lookup argument) refers to the Exact match of the lookup value.
    • Output → 243

Using VLOOKUP and MATCH Functions

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

How to Map Data in Excel Vlookup Using VLOOKUP and MATCH Functions


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.

Dataset 3

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 the range argument that refers to the Best Seller Books. Following, F4 represents the criteria argument, which returns the count of the matched value.
    • Output → 1
  • VLOOKUP(F4,B5:C9,2,TRUE) →  Here, F4 ( lookup_value argument) is mapped from the B5:C9 (table_array argument) array. Next, 2 (col_index_num argument) represents the column number of the lookup value. Lastly, TRUE (range_lookup argument) refers to the 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 the logical_test argument which prompts the IF function to return 25 (value_if_true argument) otherwise it returns 0 (value_if_false argument).
    • Output → $25

Using VLOOKUP and COUNTIF Functions

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

How to Map Data in Excel Vlookup Using VLOOKUP and COUNTIF Functions


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.

Dataset 4

Steps:

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

How to Map Data in Excel Vlookup Using VLOOKUP and INDIRECT Functions

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.

How to Map Data in Excel Vlookup Using VLOOKUP and INDIRECT Functions

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

How to Map Data in Excel Vlookup Using VLOOKUP and INDIRECT Functions

This opens the Edit Name wizard.

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

How to Map Data in Excel Vlookup Using VLOOKUP and INDIRECT Functions

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

How to Map Data in Excel Vlookup Using VLOOKUP and INDIRECT Functions

Likewise, define the Named Range for Denver.

How to Map Data in Excel Vlookup Using VLOOKUP and INDIRECT Functions

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

How to Map Data in Excel Vlookup Using VLOOKUP and INDIRECT Functions

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

How to Map Data in Excel Vlookup Using VLOOKUP and INDIRECT Functions

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

How to Map Data in Excel Vlookup Using VLOOKUP and INDIRECT Functions

  • 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 the ref_text argument that refers to the Named Range Boston.
  • VLOOKUP(B14,INDIRECT(C14),2,FALSE) → Here, B14 ( lookup_value argument) is mapped from the Named Range, INDIRECT(C14) that is the table_array argument. Next, 2 (col_index_num argument) represents the column number of the lookup value. Lastly, FALSE (range_lookup argument) refers to the Exact match of the lookup value.
  • Output → $1.2

How to Map Data in Excel Vlookup Using VLOOKUP and INDIRECT Functions

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

How to Map Data in Excel Vlookup Using VLOOKUP and INDIRECT Functions

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.

Dataset 5

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.

How to Map Data in Excel Vlookup Using VLOOKUP Function

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

How to Map Data in Excel Vlookup Using VLOOKUP Function

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

How to Map Data in Excel Vlookup Using VLOOKUP Function


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 ( lookup_value argument) is mapped from the B5:D14 (table_array argument) array. Next, 3 (col_index_num argument) represents the column number of the lookup value. Lastly, FALSE (range_lookup argument) refers to the 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 the logical_test argument which prompts the IF function to return the value from VLOOKUP function (value_if_true argument) otherwise it returns blank “”  (value_if_false argument).
    • Output → HR

How to Map Data in Excel Vlookup Using VLOOKUP Function

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

How to Map Data in Excel Vlookup Using VLOOKUP and IF Functions

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.

Using VLOOKUP and IF Functions


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.

Practice Section


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Eshrak Kader
Eshrak Kader

Eshrak Kader is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He boasts a rich background in Microsoft Office Suite, and over the past year, he has authored over 120 insightful articles for the ExcelDemy. Currently, Eshrak leads a team of 5 in the ExcelDemy Video project and enjoys problem-solving and making videos on Excel. Eshrak’s passion for continuous learning underscores his commitment to excellence in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo