XLOOKUP multiple items from one column, returning CHOOSECOLS

[Vcoppens]

Hi,

From about 30000 rows and 50 columns, I need to extract data based on multiple numbers from one particular column, called TYPE.

There are about 20 different types, and I only need to extract types 1 - 3 - 5 and 7.

My formula works perfectly well when I look up, for example, number 1.

=xlookup(A1,F:F,choosecols(A:BD,1,5,2,20,27,17,21,34,19)

However, when I lookup the array with TYPE numbers from 1 through 7, I get a #SPILL! error.

Lookup the array of numbers in column F.

1

3

5

7

The result would return a compacted table:

Number	Name	Amount	Original Amount	Date	Percentage

If anyone has a suggestion, I would greatly appreciate it.

Thank you!

 

[Fluff]

Alex had an extra bracket, try

=FILTER(CHOOSECOLS(A:BD,1,5,2,20,27,17,21,34,19),ISNUMBER(MATCH(F:F,{1,3,5,7},0)),"")

 

[Vcoppens]

Alex had an extra bracket, try

=FILTER(CHOOSECOLS(A:BD,1,5,2,20,27,17,21,34,19),ISNUMBER(MATCH(F:F,{1,3,5,7},0)),"")

Thank you so very much to all of you for helping me out.

This worked like a charm. Thank you Alex for setting us on the right path.

Thank you, thank you!

 

[Alex Blakenburg]

You're welcome. You can use your Table "Types" where we have the hardcoded criteria array {}