Hi,

Im trying to create a formula where based on partial text it would return value but can't get anything to work properly :D. I have a 2 column table (multiple rows), column 1 has text and values and column 2 is to be filled. Text in column 1 varies but has specific text (partial) based on which I input value into column 2. That is:

​

Based on above example, I would like to input formula in 2nd column which would search column 1 for specific text and return specific values in column 2. I have 10 conditions total which have 10 corresponding values to return. Text/numbers that come before and after searched text are irrelevant but searched text is not always in same spot so cannot use something like LEFT/RIGHT formulas (although searched text always is the same, e.g. "NL") I tried to use IF(OR(ISNUMBER(SEARCH, VLOOKUP, INDEX, IFERROR or TEXTJOIN but every one of them works with searching for 1 specific partial text and returns specific value, but I cant manage to nest all 10 into one formula. For VLOOKUP I made 2nd worksheet with table with text/value, but still didnt manage for it to work

Like =IFERROR(VLOOKUP("NL",TAB!A2:B12,2,FALSE),VLOOKUP("UK",TAB!A2:B12,2FALSE)... etc

I wanted for it to remain formula based rather than VBA or other macro oriented

​

If someone has idea how to get this to work, it would be much appreciated.