Forum Discussion
Solved
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 ...
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).
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!