Log In
E-mail or User ID
  Keep me signed in
Recover Password
Create an Account

Excel Quick Tip of the Week-How to find a perfect match in a lookup table

A lot of uses for Excel's VLOOKUP and HLOOKUP functions do not require an exact match. For example, when they are used to find the shipping cost for a particular item, the function searches for the shipping cost that corresponds to the value in the lookup column that most closely matches the price of the item. However, some situations require an exact match, such as when you use a lookup table to find the name that correctly matches a customer ID number. In such cases, you would need to add the FALSE argument at the end of the VLOOKUP or HLOOKUP statement. For example, suppose you have a list of Customer ID Numbers in Column A and their corresponding names in Column B as shown:



Lookup Ex1



To create a lookup formula that matches the correct name to a customer's ID, follow these steps:


  1. Select A1:B20

  2. Click in the Name box of the formula bar and enter Customer_List. and then press the Enter key.

  3. In D1, enter What is the Customer ID?

  4. In D2, enter The Customer's Name is

  5. In E2, enter this formula:




Now, when a customer ID number is entered in E1, and that number exists in Customer_List, the name of the customer will be displayed in E2, as shown:


Lookup Ex2


Now, let's say we enter 121 in E1. Without the FALSE argument, the VLOOKUP statement would incorrectly display Eric Henandez, as 121 is the closest number to 120. However, with the FALSE argument VLOOKUP will return the #NA error as shown:




Lookup Ex3


The VLOOKUP statement with the FALSE argument does provide an exact match! However, we would not want our users to receive #NA errors. To make this spreadsheet easier to use, let's replace the formula in E2 with the this formula:


=IF(ISNA(VLOOKUP(E1,Customer_List,2,FALSE)),"Name not found. Please try again.",VLOOKUP(E1,Customer_List,2,FALSE))


This IF statement will be true if the ISNA function is True. Here the ISNA function is true when the value of VLOOKUP is #N/A. So, when a name matches the customer ID number, the ISNA function is false, and the name found by VLOOKUP is displayed. If there is no exact match to the customer ID number entered, the message “Name not found. Please try again.” is displayed as shown:



Lookup Ex4


Read 2 comments


What a handy little tip! Totally unaware that I could add in the ISNA part to a VLOOKUP to avoid seeing the dreaded #N/A. :) Thanks for the post, Mary Ann!
Your welcome, Amanda! I hope to do more of these in the coming weeks.

Leave a Comment

Connect to this blog to be notified of new entries.
You are not logged in.
Sign In to post unmoderated comments.
Join the community to create your free profile today.
Want to read more from Mary Ann Richardson? Check out the blog archive.
Archive Category: Excel Tips
Keyword Tags:  Excel   Lookup tables   IF   ISNA

Follow a long-time IT Trainer/Analyst as she explores the trends and issues concerning users seeking better returns from their IT ... more
Receive the latest blog posts:
Share Your Perspective