VLOOKUP To Two Tables
May 23, 2018 - by Bill Jelen
Today's question from Flo in Nashville:
I need to do a VLOOKUP for a series of item numbers. Each item number is either going to be found in Catalog A or in Catalog B. Can I write a formula that first searches Catalog A. If the item is not found, then move on to Catalog B?
The solution involves the IFERROR function introduced in Excel 2010 or the IFNA function introduced in Excel 2013.
Start off with a simple VLOOKUP that searches the first catalog. In the image below, Frontlist is a named range pointing to data on Sheet2. You can see some items are found, but many are returning the #N/A error.
To handle the situations where the items are not found in the first catalog, wrap the VLOOKUP function in the IFERROR function. The IFERROR function will analyze the results of the VLOOKUP. If VLOOKUP successfully returns an answer, that will be the answer returned by IFERROR. However, if the VLOOKUP returns any error, then IFERROR will move on to the second argument, called Value_if_Error. While I often put zero or "Not Found" as the second argument, you could have a second VLOOKUP specified as the Value_if_Error argument.
The formula shown above will first look in the Frontlist for a match. If it is not found, then the Backlist table will be searched. As Flo described, each item is either found in Frontlist or Backlist. In this case, the formula returns a description for each item in the order.
Learn Excel from MrExcel Podcast 2208: VLOOKUP to Two Tables
Hey, welcome back to the MrExcel netcast; I'm Bill Jelen. Today's question from Flo in Nashville. Now, Flo has to do a bunch of VLOOKUPs, but here's the deal: Each of these part numbers is either found in Catalog 1, the Frontlist catalog, or it's found in Catalog 2. So, Flo wants to first look in the Frontlist, and if it's found, beautiful, just stop. But if it's not, then move on and check the Backlist. So, this is going to be easier thanks to a new function that came along in Excel 2010 called IFERROR.
Alright, so we're going to do a regular =VLOOKUP(A4,Frontlist,2,False). By the way, that is a name range there; I created a name range for Frontlist and one for Backlist. Right, so Frontlist: Just choose that whole name; click in there-- "Frontlist," one word, no space. Same thing here-- choose the whole second catalog. Click in the name box, type Backlist, press Enter (no space). Alright, so you see that some of these work, and some of them don't. For the ones that don't, we're going to use a function that came along in Excel 2010 called IFERROR.
IFERROR's pretty cool. It lets the VLOOKUP happen, and if the first VLOOKUP works, it just stops; but, if the first VLOOKUP returns an error-- either an #N/A, like in this case, or a /0, or anything like that-- then we're going to move on to the second piece-- the value of error. And, while most of the time, I put something in there like "Not Found," this time, I'm actually going to do another VLOOKUP. So, =VLOOKUP(A4,Backlist,2,False). So, that closes the Value of Error, and then another parentheses-- that one in black-- to close the original IFERROR. Press Ctrl+Enter, and what we get is all of the answers, either from Table 1 (the Frontlist Catalog), or from Table 2 (the Backlist Catalog).
Cool, cool trick-- great idea from Flo-- never thought about doing that, but it makes a lot of sense if you have two catalogs. I suppose you could even wrap it, if there was a third catalog, right? You could even wrap this VLOOKUP in an IFERROR and then have yet another VLOOKUP, and we'll just keep chaining right down the list, going to Catalog 1, Catalog 2, Catalog 3-- beautiful, beautiful trick.
Alright, now-- VLOOKUP-- covered in my book, MrExcel LIVe: The 54 Greatest Excel Tips of All Time. Click that "I" in the top right-hand corner for more information.
OK, wrap-up from this episode. Flo from Nashville: "Can I VLOOKUP into two different tables?" Look for the item in Catalog 1-- if it's found, then great; if it's not, then move on and do a VLOOKUP in Catalog 2. So, my solution: Start with a VLOOKUP that looks up the first catalog, but then wrap that VLOOKUP in the IFERROR function that was new in Excel 2010. If you have Excel 2013, you could even use the IFNA function, which will do pretty much the same thing. The second piece of that is what to do if it's false; well, if it's false, then go do the VLOOKUP into the Backlist catalog. Cool idea from Flo-- great question from Flo-- and I wanted to pass that along.
Now, hey, to download the workbook from today's video, visit the URL down there in the YouTube description.
I want to thank Flo for showing up at my seminar in Nashville, and I want to thank you for stopping by. I'll see you next time for another netcast from MrExcel.
Download Excel File
To download the excel file: vlookup-to-two-tables.xlsx
Excel Thought Of the Day
I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:
"And one from Sun Tzu’s Art of War: With many calculations, one can win; with few one cannot. How much less chance of victory has one who makes none at all!"
Title Photo: Marc Sendra martorell on Unsplash