CODEWITHSUNDEEP
google-sheetsarray-formulas
chocofan
chocofan

Reputation: 57

Use Google Sheets vlookup to return the value in an index range

I'm working on creating a weekly update sheet of client projects.

The sheet I am pulling from has client names in one of three columns. The client name will only be in one of these three columns.

In essence, I want to consolidate these names into a single column in my weekly update sheet.

My current formula is:

=ArrayFormula(IFERROR(VLOOKUP($A32,Import!$A$3:$P$1000,{2,3,4},false),))

The only problem here is that while it searches across three columns it also requires 3 columns for output.

Is there a way that the formula can be written to consolidate the data into a single column?

Upvotes: 0

Views: 471

Answers (1)

Chris Hick
Chris Hick

Reputation: 3094

Does this formula work as you want:

=SUBSTITUTE(JOIN("|",ArrayFormula(IFERROR(VLOOKUP($A32,Import!$A$3:$P$1000,{2,3,4},0),))),"|",)

It replaces the blank columns with | and then substitutes those for blanks. This leaves only the value from one of columns B:D on your Import sheet

Upvotes: 1

Related Questions