Forum Discussion

Douglas_Emmett's avatar
Douglas_Emmett
Copper Contributor
May 27, 2024

selecting a value based on criteria from three columns

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

A1B1C1D1E1F1G1H1I1J1K1L1M1
ABC-DEF-GHIJ-0YSTARTRED NT N5/17/202406:21POUT 
BCD-EFG-HIJK-0YSTOP

GREEN

BDSF C      
CDE-FGH-IJKL-0YSTARTBLUE         

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

 

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    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.

     

Resources