Multiple IF Statements & VLOOKUPs in Formula

Karen Hansard
Karen Hansard ✭✭✭✭
edited 11/15/22 in Formulas and Functions

I am trying to populate a column, X, using IF and VLOOKUP functions on 3 unique columns but the formula only seems to be returning the correct value for 2 of the 3 columns. Background - Only one of the following 3 columns will ever be populated with a VP name. I'm using 3 different columns, one for each dept, as each dept has a unique set of VPs and data is coming in on a form. The Lookup table has all of the VP names in column 1 and associated org name in column 3.

Column A - dept A VP names

Column B - dept B VP names

Column C - dept C VP names

Column X - Org Name

Here's my formula in column X:

=IF([A]@row <> "", VLOOKUP([A]@row, {Lookup table Range 2}, 3), IF([B]@row <> "", VLOOKUP([B]@row, {Lookup table Range 2}, 3), IF([C]@row <> "", VLOOKUP([C]@row, {Lookup table Range 2}, 3, false))))

Currently, a value is correctly returned when column A and C are populated. An invalid value is returned when column B is populated. I'm using the value_if_false parameter to query/vlookup next non-blank field.

Can anyone see an issue(s) with my formula/approach?



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!