Alternative Formula for Nested If/and Function

[amp7394]

I am currently trying to find a formula that allows me to return a text value based on the string of text in another cell if the cell fits a certain criteria. For example:

Cell A1: CIG-NA-NA / BSC-NCORP-A/ PLT-NA-NA/ GLD-NCORP-A

Based on this information, I want cell B1 to say: Non-Corp Gold

Cell A2: BSC-NCORP-A / GLD-NCORP-U / CIG-NA-NA / PLT-NCORP-A

B2: Non-Corp Platinum

(there are multiple possibilities for each cell containing the string of information)

I tried using a nested If formula (including and/or) but there are 35 possible outcomes that can result from a cell similar to A1 and A2 and Excel does not allow for this many If scenarios in one formula. Is there another formula that will work for this situation?

 

[Scott Huish]

I'm not sure what all your possibilities are, but have a look at the VLOOKUP function:
https://support.office.com/en-us/article/VLOOKUP-function-0BBC8083-26FE-4963-8AB8-93A18AD188A1

 

[Taul]

Hi,
Welcome to the forum.
Am I correct in assuming the outcome “Non-Corp Gold” is defined by the last 11 characters of the text in cell A1. This appears to be true based on the two examples you have given.

If the above assumption is correct:-

1. Crate a list of the last 11 characters elsewhere on the worksheet, in the formula example I have chosen G1:G35
2. Next to these last eleven characters type the words you want to use, like “Non-Corp Gold” so they occupy the cells H1:H35

Use the following formula in cell B1

 =VLOOKUP(RIGHT(A1,11),$G$1:$H$35,2,FALSE)

If the assumption about the last eleven characters is wrong, you could use the whole text combo from column “A” and type it into column “G”, in which case the formula would be

 =VLOOKUP(A1,$G$1:$H$35,2,FALSE)

Cheers
Paul.