How to Search Multiple Values from single cell

Question

In screenshot you can see the Formula and my tables.

As per current formula if you enter "51035" in C4, the Description field in D4 automatically result "Deformed Steel Bar 10MM", (I created another sheet for the code, description and other related things).

Now I want that if I enter "51030" in C4, the result in description is shows "Deformed Steel Bar 10MM" but also if I enter 10MM instead of 51030 in C4, I want the same result.

I can use this formula

=IF(ISNUMBER(SEARCH("51030",C4)),(Value!$F$4),

IF(ISNUMBER(SEARCH("10MM",C4)),(Value!$F$4)

))

But I want a single line formula instead of 2 different lines.

Answer 1

I am not sure if I understood correctly but if your aim is to convert your formula to a single IF formula, then you may use OR keyword:

=IF(OR(ISNUMBER(SEARCH("51030",C4)), ISNUMBER(SEARCH("10MM",C4))),(Value!$F$4), "Not Found")

OR keyword takes multiple parameters inside and if one of them is TRUE then OR statement returns a TRUE. And in your case, when one of the criteria are TRUE, IF statement returns $F$4 value from the Sheet:Value.

Answer 2

Weel, I did it this way with INDEX() and MATCH() faking up some data as per the image. Enter either 10mm or 12mm or 51030 etc will give the result. Only works for the values that exist - the error function will show "error" if nothing matches.