Need solution for too many nested if statements

[Monia]

Hello everyone,

I need to find an alternative formula to "IF", because my need now surpasses the maximum allowed nested if statements.

Cel C21 contains color names and cel D21 needs to produce the color code associated to the color name in C21.

There are over 100 color names.

I used to use IF(C21="Walnut", "HZ-0854", IF(C21="Russet Olive", "2021-3", "")) and so on.
But the available colors now surpass the allowed IF statements and I don't know what to do.

Can anyone help? Here are a few colors and their codes if this helps to demonstrate how the formula should be:

Dark Malt = 6008
Whisky Gold = 6005
Cinnamon = C90120-414
Golden Maple = C90777-2
Mountain Timber = C90768-415-02
Amber = 88006-1
Heritage Oak = 151
Amber Walnut = 060
Southern Maple = 2211-1
Ash Acacia = ND2167

As mentioned before, there are over 100 colors.. so the simplest way to this would be appreciated.

Thanks everyone!

 

[NeonRedSharpie]

Place that list you have (plus the other 90) into another sheet and use a vlookup or index/match function to pull them in.

 

[Monia]

I have never used a vlookup or index/match function. Which one would be easiest for a beginner? And can you give me an idea of what the formula would look like or how it works?

 

[t0azt]

[TABLE="class: grid, width: 210"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Dark Malt[/TD]
[TD]6008[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Whisky Gold[/TD]
[TD]6005[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Cinnamon[/TD]
[TD]C90120-414[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Golden Maple[/TD]
[TD]C90777-2[/TD]
[/TR]
</tbody>[/TABLE]

If your table looks like this then the formula for D21 is this:

=VLOOKUP(C21,A:B,2,0)

Vlookup is quite simple: the first value is the one you want to find in the table (C21), the second value is the table you want to find it in in this case A:B. (It´s important to point out that the formula only looks for values on the leftmost column so if you had information in column A then the color in Column B and the code in column C then the table would be B:C.) The 3rd value is the column within table that you want your result to come from. The last one is wheather you want a close match (1) or an exact match (0). I hope that is clear enough.

 

[Monia]

This is fantastic, thanks t0azt! Though it took me forever to enter all the data, the lookup fonction does exactly what I needed.
And your explanation helps understand it, so I will be able to use it again in the future.
Again, THANKS!