Best practice formula for TRUE condition

[gsvirdi]

Hello friends,

I faced a situation where I was to check for a condition, and based on that condition I will be needed to fetch another value from the same data table. My choice was obvious VLOOKUP().....

But as expected the situation is not so kind hearted, now the new requirement is that I need to search on the bases of "Word+wild card characters".
For example I need to 1st check if the Target cell contain the word "AIR" or "STEAM", if yes then fetch the value in VLOOKUP(). Disgusting? I feel the same way too.

Hope u have understood the situation, so here's my question "If I should FIND() to check for the presence of "AIR" or "STEAM" in the contents of the target cell, then what WAY should be the best practice for this FIND() statement?

I'd request you to figure out a formula urself, otherwise seeing my useless formula may limit ur options.

I did something like this, f my vlookup_value cell is having "AIR" or "STEAM" anywhere in it, + J5>425, + I5>40..... then only I should fetch the value!!!!

,IF(AND(OR(ISNUMBER(FIND("AIR",'[Copy_BIB_Instrument List P0_20160530.xlsx]PI'!E5)),ISNUMBER(FIND("STEAM",'[Copy_BIB_Instrument List P0_20160530.xlsx]PI'!E5))),'[Copy_BIB_Instrument List P0_20160530.xlsx]PI'!J5>425,'[Copy_BIB_Instrument List P0_20160530.xlsx]PI'!I5>40),'[Copy_BIB_Instrument List P0_20160530.xlsx]PI'!B5,"")

Copy_BIB_Instrument List P0_20160530.xlsx is an external file which I'll get from our Mechanical department, I have simply copied it (unchanged) in my computer (same directory) so that whenever it changes I need not worry about My File.

 

[Nebu]

Hi:

You can use something like Lookup(2^15, search(keyword range, value),keyword flag) if you can create a list of all keywords and can give an if condition accordingly on this flag.If this does not help upload a file with some sample data along with expected output.

Thanks