VLOOKUP Question (Reference Sheet and Blanks)

[GKGK13]

Hello,

I need help creating a VLOOKUP formula to fill Sheet 1.  This is based on reference data that is contained within Sheet 2.  An example of what the results should look like is shown within Sheet 3.

I do not want the formula to populate any #N/A or 0.  I need the cells to be blank if no data is found.

Sheet 1 (DATASET)

Sheet 2 (REFERENCE DATA)

Sheet 3 (EXAMPLE - FINAL)

Thanks!

[#2]

Try this:

=FILTER(Tabelle2!$B$2:$D$4,Tabelle2!$A$2:$A$4=B2)&""

[#3]

I copied that exact formula into Sheet 1, Cell C2 and got a message saying "That function isn't valid".

I'm not familiar with the Filter function. What is Tabelle2?

[#4]

@GKGK13 

Tabelle2 is a sheet name in German.

What version of Excel do you use?

[#5 · Accepted solution]

@GKGK13 

FILTER is available in Microsoft 365 and Office 2021, not in older versions.

In C2:

=IFERROR(IF(VLOOKUP($B2,'REFERENCE DATA'!$A$2:$D$4,COLUMN()-1,FALSE)="","",VLOOKUP($B2,'REFERENCE DATA'!$A$2:$D$4,COLUMN()-1,FALSE)),"")

Replace REFERENCE DATA with the actual name of the data sheet.

Fill to the right to column E, then down to row 4 (or vice versa).

[#6]

This worked. Thank you @HansVogelaar 

[#7]

@GKGK13 

So my solution will be even less use to the OP !

= REDUCE(Group, {1,2,3},
    LAMBDA(acc,k,
      HSTACK(acc, XLOOKUP(Group, groupRef, CHOOSECOLS(results,k)))
    )
  )

Mind you, it does provide another opportunity to take a dig at Microsoft for failing to deal with this everyday issue!  Clearly the formula should read

= XLOOKUP(Group, groupRef, results)

OK, perhaps that is a bit harsh because the result is an array of Ranges, but I certainly expect arrays of arrays to be fundamental objects that occur in most formulae!