Explanation
In this example, the goal is to set up VLOOKUP to retrieve costs based on a variable vendor name. In other words, we want a formula that allows us to switch tables dynamically based on a user-supplied value. There are two cost tables in the worksheet, one for Vendor A and one for Vendor B. Both tables are defined as the named ranges vendor_a (B5:C8) and vendor_b (B11:C14).
At the core, this is a basic lookup problem, and we could use the VLOOKUP function to get the cost for a color like this:
=VLOOKUP(E5,"vendor_a",2,0) // vendor a cost for red
=VLOOKUP(E5,"vendor_b",2,0) // vendor b cost for red
These formulas work fine, but the table name provided to VLOOKUP is hard-coded, not variable.
In thinking about how to make the table variable, notice the table names are identical except for the last letter (“a” or “b”). This means we can assemble the correct table for each vendor with concatenation like this:
="vendor_"&"a" // returns "vendor_a"
="vendor_"&"b" // returns "vendor_b"
And, since “a” and “b” are already in column F, we can pick up that value directly:
="vendor_"&F5 // "vendor_a"
The above expression will correctly create the vendor name we need to perform a lookup. However, the formula below will fail with a #VALUE! error:
=VLOOKUP(E5,"vendor_"&F5,2,0) // returns #VALUE!
Why is that? The formula above fails because Excel interprets the table as a text value , not a range . What we need is a way to tell Excel to interpret the text value like a cell reference. This is a job for the INDIRECT function which is designed to evaluate a text value as a reference. Once we wrap the original expression in INDIRECT, we’ll get a proper reference:
=INDIRECT("vendor_"&F5)
=INDIRECT("vendor_a)
=B5:C8
Carrying these changes into the final formula, we have:
=VLOOKUP(E5,INDIRECT("vendor_"&F5),2,0) // returns 9.95
Now VLOOKUP will correctly look up the cost for Vendor A or B, depending on the letter entered in column F. In the worksheet as shown, the formula returns $9.95, since the cost for Red from Vendor A is $9.95 . If the vendor is changed to “b”, VLOOKUP will dynamically switch tables and return $12.50.
With the IF function
The example above is a nice illustration of the power of setting up a worksheet with consistently named tables, but this isn’t strictly necessary to perform a lookup with a variable table name. For example, we could just use the IF function to swap tables like this:
=VLOOKUP(E5,IF(F5="a",vendor_a,vendor_b),2,0)
Or, without named ranges:
=VLOOKUP(E5,IF(F5="a",$B$5:$C$8,$B$11:$C$14),2,0)
Here, the IF function simply checks the value in column F and returns one range if the letter is “a”, and another if not. This approach won’t scale as well (the formula will become progressively more complex as we add more vendor tables) but it works fine. It also nicely demonstrates how one function can be nested inside another to deliver a range instead of a single value.
Explanation
When VLOOKUP can’t find a value in a lookup table, it returns the #N/A error. In this example, the goal is to remove the #N/A error that VLOOKUP returns when it can’t find a lookup value. In general, the best way to do this is to use the IFNA function. However, the IFERROR function can also be used in the same way. Both options are explained below.
VLOOKUP function
The VLOOKUP function performs a lookup operation on vertical data. The generic syntax for VLOOKUP looks like this:
VLOOKUP(A1,table,column,FALSE)
Where A1 contains a value to lookup, table is the data, column is a number, and FALSE specifies exact match, which is required in this example. In the workbook shown, we want to enter an abbreviation in cell E5 and get the correct State name in cell F5, where all data is in an Excel Table named data . To do this, we can use VLOOKUP in a formula like this:
VLOOKUP(E5,data,2,FALSE)
When a valid 2-letter code is entered in cell E5, VLOOKUP will return the corresponding State. For example, if “CA” is entered in cell E5, VLOOKUP will return “California”:
VLOOKUP("CA",data,2,FALSE) // returns "California"
If an invalid code is entered in cell E5, VLOOKUP will return an #N/A error:
VLOOKUP("XX",data,2,FALSE) // returns #N/A
The screen below shows how this error looks on the worksheet:
The #N/A error technically means “not available”. However, you may want to return a more friendly result. You can do this by combining VLOOKUP with the IFNA function.
VLOOKUP with IFNA
The IFNA function is a simple way to trap and handle #N/A errors without catching other errors. When used with a formula, the generic syntax looks like this:
=IFNA(formula,alternative)
Here, the formula might return a #N/A error; the alternative is the value or formula to return in that case. To trap the #N/A error in this example, we wrap the IFNA function around the original VLOOKUP function and provide an alternate value. For example, to return “Not found” when VLOOKUP returns #N/A, we can use a formula like this:
=IFNA(VLOOKUP(E5,data,2,FALSE),"Not found")
Now, when VLOOKUP returns the #N/A error, IFNA takes over and returns “Not found”:
=IFNA(VLOOKUP("XX",data,2,FALSE),"Not found") // returns "Not found"
To return a different message, just change the second argument in IFNA:
=IFNA(VLOOKUP("XX",data,2,FALSE),"Invalid code") // returns "Invalid code"
If you would prefer to return nothing, you can provide an empty string ("") instead, like this:
=IFNA(VLOOKUP("XX",data,2,FALSE),"") // returns ""
The result from this formula will look like an empty cell when the lookup Code is not found.
VLOOKUP with IFERROR
You can also trap #N/A errors returned by VLOOKUP with the IFERROR function like this:
=IFERROR(VLOOKUP(E5,data,2,FALSE),"Not found")
IFERROR works just like the IFNA function — it catches an #N/A error returned by VLOOKUP and returns an alternative result. The difference is that IFERROR will catch other errors as well.
The #N/A error is Excel’s way of telling you a value was not found. With the more general IFERROR function, there is a risk that you might catch other unrelated errors and return a confusing result. For example, if you have a typo in a function name, Excel will return a #NAME! error. Or, if rows/columns are deleted in a worksheet, a formula may return a #REF! error. While IFNA will let these errors come through (so you see them), IFERROR will catch these errors, too, which can hide the underlying problem. For these reasons, I recommend you use the IFNA function when the purpose is to trap #N/A errors only.
Older versions of Excel
In earlier versions of Excel that lack the IFNA function, you will need to repeat the VLOOKUP inside an IF function that catches an error with the ISNA function . For example:
=IF(ISNA(VLOOKUP(A1,table,2,FALSE)),"Not found",VLOOKUP(A1,table,2,FALSE))
