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))

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

anysheet, then you should indeed use ISNA for Sheet5 and returnsomething, perhaps some text in the cell telling the user that the value was not found.

Ask Your Question

Weekly Poll