Put a specific value if cell not empty and if no information with a formula in an excel report

This formula helps me to put a specific value in a cell only if there is no match. For instance, imagine that I need to know the right city of a country but I don’t have this information in my main report and I need to check into another sheet, if this information doesn’t exit, put a specific value.

formula formula


When I use the formula ?

To put a particular result if 2 different cells are not matching.


How to use the formula ?

The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".


How are the formulas ?




=IFERROR(VLOOKUP(B2,Sheet2!A:C,2,0),"not specified")

As you can see:

  • C2 is showing “not specified” because there is no ID
  • C3 is showing “Brazil” because the same ID exists in the sheet2 and the location of the sheet2 for this ID is “Brazil”


  • B2 asking to match this value
  • Sheet2!A:C,2 asking to search in the sheet2 in column A, if match, look in the column B (2) the location and take this value, if no match, put “not specified”

Interesting Management