selecting a value based on criteria from three columns

[Douglas_Emmett]

I have a table with 13 columns. Two additional columns need to be calculated based on values in columns 1-13. Specifically as follows:

If G2="C" and if B2="Y" and E2="", then the column M1 Value should = "HIG"

If G2="C" and if B2="" and E2="", then the column M1 Value should = "TP"

If G2="C" and if B2="Y" and E2="CTIM", then the column M1 Value should = "TP"

If G2="T" and if B2="Y" and E2="", then the column M1 Value should = "GFP"

If G2="T" and if B2="" and E2="", then the column M1 Value should = "SP"

If G2="T" and if B2="Y" and E2="BDSF", then the column M1 Value should = "SP"

If G2="O" then the column M1 Value should = "C"

If G2="C" and if B2="Y" and E2="", then the column M1 Value should =

If G2="" and if A2=not Blank, then the column M1 Value should = Error

A1

B1

C1

D1

E1

F1

G1

H1

I1

J1

K1

L1

M1

ABC-DEF-GHIJ-0

Y

START

RED

N

T

N

5/17/2024

06:21P

OUT

BCD-EFG-HIJK-0

Y

STOP

GREEN

BDSF

C

CDE-FGH-IJKL-0

Y

START

BLUE

I am thinking that a VLOOKUP would probably work best, but my brain just can't close the loop. Thanks

[#2]

@Douglas_Emmett To avoid a great number of nested IF statements it is indeed best to work with a lookup table. The attached file contains a possible solution using VLOOKUP. If you are on a modern Excel version, however, I would recommend to use XLOOKUP in stead. See if you can apply this method to your real data.

Note that your example data for row 3 has C-Y-BSDF in columns G-B-E. That particular sequence does not exist in the list of conditions. Thus, VLOOKUP returns #N/A.