Forum Discussion
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
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
- Riny_van_EekelenPlatinum 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.