SOLVED

# VLOOKUP Question (Reference Sheet and Blanks)

Copper Contributor

# VLOOKUP Question (Reference Sheet and Blanks)

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!

6 Replies

# Re: VLOOKUP Question (Reference Sheet and Blanks)

Try this:

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

# Re: VLOOKUP Question (Reference Sheet and Blanks)

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?

# Re: VLOOKUP Question (Reference Sheet and Blanks)

Tabelle2 is a sheet name in German.

What version of Excel do you use?

best response confirmed by GKGK13 (Copper Contributor)
Solution

# Re: VLOOKUP Question (Reference Sheet and Blanks)

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).

# Re: VLOOKUP Question (Reference Sheet and Blanks)

This worked. Thank you @HansVogelaar

# Re: VLOOKUP Question (Reference Sheet and Blanks)

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!

1 best response

Accepted Solutions
best response confirmed by GKGK13 (Copper Contributor)
Solution

# Re: VLOOKUP Question (Reference Sheet and Blanks)

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).