# Logic of Nested Vlookups in Excel

Microsoft Microsoft excel 2007 (pc)
September 8, 2010 at 07:36:21
Specs: Windows XP
 The nested formula provided here http://www.computing.net/answers/of... by DerbyDad03 seems to work. I am just trying to understand why it works. What is the logic/progression that Excel is using? Why don't you need an IF(ISNA statement for the 5th sheet? I am trying to understand what the formula is doing so that I can be comfortable/confident using it. Thanks!=IF(ISNA(VLOOKUP(C2,Sheet1!A1:B2,2,0)),IF(ISNA(VLOOKUP(C2,Sheet2!A1:B2,2,0)),IF(ISNA(VLOOKUP(C2,Sheet3!A1:B2,2,0)),IF(ISNA(VLOOKUP(C2,Sheet4!A1:B2,2,0)),VLOOKUP(C2,Sheet5!A1:B2,2,0),VLOOKUP(C2,Sheet4!A1:B2,2,0)),VLOOKUP(C2,Sheet3!A1:B2,2,0)),VLOOKUP(C2,Sheet2!A1:B2,2,0)),VLOOKUP(C2,Sheet1!A1:B2,2,0))

See More: Logic of Nested Vlookups in Excel

#1
September 8, 2010 at 10:19:43
 The formula assumes that the value in C2 exists on at least one of the sheets.The reason you don't need ISNA for Sheet5 is because you have already looked for the value on Sheets 1 - 4 and it wasn't found, therefore it must be on Sheet5.If the possibility exists that the value might not be found on any sheet, then you should indeed use ISNA for Sheet5 and return something, perhaps some text in the cell telling the user that the value was not found.

Report •
Related Solutions