VLOOKUP To Two Tables

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.