Multiple IF Statements & VLOOKUPs in Formula

Karen Hansard

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?

Thanks!

Paul Newcome

Try this:

=IFERROR(INDEX({Column to Pull}, MATCH(INDEX(COLLECT(A@row:C@row, A@row:C@row, @cell <> ""), 1, 1), {Column To Match On}, 0)), "")

Karen Hansard

Thanks Paul. I'll give it a try.