Multiple IF Statements & VLOOKUPs in Formula
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 nonblank field.
Can anyone see an issue(s) with my formula/approach?
Thanks!
Answers

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)), "")

Thanks Paul. I'll give it a try.
Help Article Resources
Categories
Check out the Formula Handbook template!