FILTER from a few sheets

[kumara_faith]

Hi,

I have the following sample data located in Sheet1:

Book1
	B	C	D	E	F
3	Policy No	Name	Payment Type	Last Payment Date	Payment Amount
4	123456	Jay	6	1/4/2022	200
Sheet1

Then, I have the following sample data in Sheet2:

Book1
	H	I	J	K	L
3	Policy No	Name	Payment Type	Last Payment Date	Payment Amount
4	123457	May	6	2/5/2022	315
Sheet1

In Sheet3, I am trying to build a lookup to pull the data from Sheet1 and Sheet2 if the policy number matches:

Book1
	O	P	Q	R	S
3	Policy No	123456
4
5	Policy No	Name	Payment Type	Last Payment Date	Payment Amount
6	123456	Jay	6	44652	200
Sheet1

Cell Formulas
Range	Formula
O6:S6	O6	=FILTER(B4:F4,B4=P3)
Dynamic array formulas.

I managed to look the data from Sheet1 but how do I modify the FILTER formula to look at both Sheet1 and Sheet2 to check if the policy number matches and populate the matching data accordingly ? Appreciate any help.

 

[kumara_faith]

Hi,

I have a cross post here:

Filter Function Across Multiple Sheets

Hi, I have a data table in Sheet1 and Sheet2. In Sheet3, I am trying to build a lookup that will populate the relevant information from Sheet1 and Sheet2 if the policy number matches using a FILTER function. Is this possible or by any other formula ? Appreciate any help. I have a cross...

www.excelforum.com

 

[Fluff]

How about

=LET(vs,VSTACK(B4:F100,Sheet2!B4:F100),FILTER(vs,INDEX(vs,,1)=P3))

 

[kumara_faith]

Hi Fluff,

Thank you and that worked as well. Appreciate your patience and have a great day.

 

[Fluff]

Glad to help & thanks for the feedback.