Put a specific value if match an ID and find a specific word with a formula in an excel report

For one of my reports, I needed to have a formula to fill up a cell with a specific word only if no match can’t be found and most importantly, if it finds a particular word in the description. For example, if the formula finds the name of a provider in the description, I needed to fill the cell that the configuration item type is an external system but first, the formula should check in another sheet if another information exists or not.

macro
macro

 

When I use the formula ?

It may happen that the information is missing in the main report so I need this formula to look for the correct information from another extracted report.

 

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 ?

=IF()

=IFERROR()

=VLOOKUP()

=SEARCH()

=IFERROR(IF(VLOOKUP(B2,Sheet2!A:D,3,0)=0,"Internal System",VLOOKUP(B2,Sheet2!A:D,3,0)),IFERROR(IF(SEARCH("*provider*",C2,1),"Provider System"),"Internal System"))

As you can see:

  • D2 is showing “provider system” because there is no ID and “provider” is in the description
  • D3 is showing “test2” because the same ID exists in the sheet2 and the type of the sheet2 for this ID is “test2”
  • D4 is showing “internal system” because there is no ID and not “provider” in the description
  • D5 is showing “internal system” because the ID doesn’t exist in the sheet2

Explanation:

  • IFERROR(IF(VLOOKUP(B2,Sheet2!A:D,3,0)=0,"Internal System",VLOOKUP(B2,Sheet2!A:D,3,0))
    Asking the formula to match the ID in the sheet2 in column A, if match, look in the column C (3) the type and take this value, if no match, go next
  • IFERROR(IF(SEARCH("*provider*",C2,1),"Provider System"),"Internal System")
    Asking the formula to find in the description the word “provider”, if find, put “provider system”, if no find, put “internal system”

Interesting Management