Use Vlookup with the If Function

The Vlookup function in Excel can be used with the If function to create a conditional Vlookup. Using the two functions together can create some powerful Vlookups.

Let’s suppose we want to use the Vlookup function across multiple sheets in Excel. We want to find a value on the first sheet Products1, but if it cannot be found then look for it on Products2, and if it still cannot be found then look on Products3 and so on. The method of doing this will differ depending on the version of Excel you are using.

This article will look at returning the name of a product by looking across 3 Excel worksheets. The formula will use the product ID entered in cell A3 and return the product name from column B of each sheet.

Use Vlookup with the If Function

The formula below uses the ISNA and IF functions with Vlookup to search across multiple worksheets.

=IF(ISNA(VLOOKUP(A2,Region1!A1:B4,2,FALSE)),IF(ISNA(VLOOKUP(summary!A2,Region2!A1:B4,2,FALSE)),IF(ISNA(VLOOKUP(summary!A2,Region3!A1:B4,2,FALSE)),"",VLOOKUP(A2,Region3!A1:B4,2,FALSE)),VLOOKUP(summary!A2,Region2!A1:B4,2,FALSE)),VLOOKUP(summary!A2,Region1!A1:B4,2,FALSE))

This formula is long and looks quite daunting but only uses three functions. With practice it becomes easier to logically break down these longer formulas and assess what they do.

The ISNA function is used to check if the Vlookup function returned a #N/A error message or not. If it did then the IF function is used to run the Vlookup on the next sheet. If a #N/A message is still returned then the Vlookup is run on the next sheet. If no product ID is found then the text “Not found” is entered into the cell.

If a product ID is found then the required product name is returned.