Filter function to return multiple values while auto adjusting lookup range against filtered results

[irfananeeza]

Hi Experts,

I am using new Filter function to return multiple values from a different workbook data. The filter is does work. However, when it returns matching values against the lookup value the other lookup values right below the matching lookup value does not get pushed down. I would like some kind of formula that determines count of matching values and then automatically push down the 2nd lookup value to the end of the list. This allows me to copy/paste filter formula down to return the matching values for the second lookup and son. I am working with a huge lookup list and doing it manually is tough. I have attached a sample screen and would appreciate your help.

Thanks in advance!

Book1
	B	C	D	E	F
1	Book1_Sheet1			Book2_Lookup Array
2		FILTER($E$3:$E$12,$F$3:$F$12=B3)
3	Invoice#	Filter Formula		Vin#	Invoice#
4	ABC123	Company1		Company1	ABC123
5	DEF123	Company2		Company2	ABC123
6	GHI123	Company3		Company3	ABC123
7		Company4		Company4	ABC123
8		Company5		Company5	ABC123
9				Region1	DEF123
10				East	GHI123
11				West	GHI123
12				North	GHI123
13				South	GHI123
Sheet1

Cell Formulas
Range	Formula
C4:C8	C4	=FILTER($E$4:$E$13,$F$4:$F$13=B4)
Dynamic array formulas.

 

[Fluff]

How about

Fluff.xlsm
	B	C	D	E	F
1	Book1_Sheet1			Book2_Lookup Array
2
3	Invoice#	Filter Formula		Vin#	Invoice#
4	ABC123	Company1		Company1	ABC123
5	DEF123	Company2		Company2	ABC123
6	GHI123	Company3		Company3	ABC123
7		Company4		Company4	ABC123
8		Company5		Company5	ABC123
9		Region1		Region1	DEF123
10		East		East	GHI123
11		West		West	GHI123
12		North		North	GHI123
13		South		South	GHI123
14
Sheet5

Cell Formulas
Range	Formula
C4:C13	C4	=DROP(REDUCE("",TOCOL(B4:B8,1),LAMBDA(x,y,VSTACK(x,FILTER($E$4:$E$13,$F$4:$F$13=y)))),1)
Dynamic array formulas.

 

[Cubist]

Depending on the consistency of your data, another option:

2024 Review.xlsx
	A	B	C	D	E	F
1		Book1_Sheet1			Book2_Lookup Array
2
3		Invoice#	Filter Formula		Vin#	Invoice#
4		ABC123	Company1		Company1	ABC123
5		DEF123	Company2		Company2	ABC123
6		GHI123	Company3		Company3	ABC123
7			Company4		Company4	ABC123
8			Company5		Company5	ABC123
9			Region1		Region1	DEF123
10			East		East	GHI123
11			West		West	GHI123
12			North		North	GHI123
13			South		South	GHI123
Sheet6

Cell Formulas
Range	Formula
C4:C13	C4	=FILTER(E4:E13,ISNUMBER(XMATCH(F4:F13,B4:B6)))
Dynamic array formulas.

 

[irfananeeza]

Many thanks to both @Cubist and @Fluff. I spent time playing around with both variations.

@Cubist, I tried your formula but could not make it work, the message say's there is a problem with the formula.

@Fluff, I have tried your's. It partially worked. The formula seems to return matching results for all the lookup values combined. However, the formula does not push the lookup value down to match the return values. The screen shot shows what results I get when I use your formula vs. the result expected. Would appreciate your help. Thanks.

Book1
	B	C	D	E	F
1	Book1_Sheet1	Results from FLUFF's formula		Book2_Lookup Array
2		'=FILTER(E4:E13,ISNUMBER(XMATCH(F4:F13,B4:B6)))
3	Invoice#	Filter Formula		Vin#	Invoice#
4	ABC123	Company1		Company1	ABC123
5	DEF123	Company2		Company2	ABC123
6	GHI123	Company3		Company3	ABC123
7		Company4		Company4	ABC123
8		Company5		Company5	ABC123
9	?	Region1		Region1	DEF123
10	?	East		East	GHI123
11		West		West	GHI123
12		North		North	GHI123
13		South		South	GHI123
14
15	Result Expected:
16	Invoice#	Filter Formula
17	ABC123	Company1
18		Company2
19		Company3
20		Company4
21		Company5
22	DEF123	Region1	Celll B22 matching with it's correspoing result
23	GHI123	East	Cell B23 matching with corresponding results
24		West
25		North
26		South
Sheet1

Cell Formulas
Range	Formula
C4:C13	C4	=FILTER(E4:E13,ISNUMBER((XMATCH(F4:F13,B4:B6))))
C17:C21	C17	=FILTER($E$4:$E$13,$F$4:$F$13=B17)
Dynamic array formulas.

 

[Fluff]

You have included Cubist's formula, which you said didn't work, but not mine.

How about

Fluff.xlsm
	B	C	D	E	F
1	Book1_Sheet1			Book2_Lookup Array
2
3	Invoice#	Filter Formula		Vin#	Invoice#
4	ABC123			Company1	ABC123
5	DEF123			Company2	ABC123
6	GHI123			Company3	ABC123
7				Company4	ABC123
8				Company5	ABC123
9	?			Region1	DEF123
10	?			East	GHI123
11				West	GHI123
12				North	GHI123
13				South	GHI123
14
15	Result Expected:
16	Invoice#	Filter Formula
17	ABC123	Company1
18		Company2
19		Company3
20		Company4
21		Company5
22	DEF123	Region1
23	GHI123	East
24		West
25		North
26		South
Sheet5

Cell Formulas
Range	Formula
B17:C26	B17	=DROP(REDUCE("",TOCOL(B4:B8,1),LAMBDA(x,y,LET(f,FILTER($E$4:$E$13,$F$4:$F$13=y),VSTACK(x,HSTACK(EXPAND(y,ROWS(f),,""),f))))),1)
Dynamic array formulas.

 

[irfananeeza]

Hi @Fluff,

Thanks for your response. Sorry, it was my mistake I mixed up @Cubist and @Fluff.

@Cubist formula worked partially as it returns all matches but it would not push down the corresponding lookup value.

@Fluff, your initial formula with Lambda gave me error message saying "there is a problem with formula". But thanks your version 2, it worked beautifully. I am very thankful for your help. It is complex formula whereas I found @Cubist formula relatively easy. @Fluff, if you don't mind I follow up with @Cubist to see if he can provide me an amended formula to get the desired result because it is easier for me to understand it.

Hi @Cubist, I found your formula "=FILTER(E4:E13,ISNUMBER((XMATCH(F4:F13,B4:B6))))" relatively easy to apply. It did work but partially. Is there a possibility that you can help tweak it a bit to produce the desired result i.e. the lookup values in column B get pushed down to match the filter returned values.?

Thanks to both of you.

 

[Cubist]

Are you OK with having the Inv# on all entries? It's simpler than just the first instances. Try:

2024 Review.xlsx
	A	B	C	D	E	F
1		Book1_Sheet1			Book2_Lookup Array
2
3		Invoice#	Filter Formula		Vin#	Invoice#
4		ABC123			Company1	ABC123
5		DEF123			Company2	ABC123
6		GHI123			Company3	ABC123
7					Company4	ABC123
8					Company5	ABC123
9					Region1	DEF123
10					East	GHI123
11					West	GHI123
12					North	GHI123
13					South	GHI123
14
15		Result Expected:
16		Invoice#	Filter Formula
17		ABC123	Company1
18		ABC123	Company2
19		ABC123	Company3
20		ABC123	Company4
21		ABC123	Company5
22		DEF123	Region1
23		GHI123	East
24		GHI123	West
25		GHI123	North
26		GHI123	South
Sheet7

Cell Formulas
Range	Formula
B17:C26	B17	=FILTER(HSTACK(F4:F13,E4:E13),ISNUMBER(XMATCH(F4:F13,B4:B6)))
Dynamic array formulas.

 

[irfananeeza]

Hi @Cubist, it worked perfectly exactly what I was looking for and its relatively easy to apply. Thank you for your help.

@Fluff, I also want to thank you for your help as well. Having two options always better.

Both of you are amazing.

 

[Fluff]

glad we could help & thanks for the feedback.