How to use VBA to autopopulate index match formula in a range of cells

[valerieann]

How do I use VBA to insert this index 3 match formula into a range of a column every time we refresh the source data? I think I need to use the Evaluate function but I haven't found an example of it producing a formula instead of a value.

Formula: =IFERROR(INDEX(Source!$D$1:$D$81, MATCH(1, ($A3=(Source!$A$1:$A$81)) * ($B$1=(Source!$C$1:$C$81)) * ($B$2=(Source!$B$1:$B$81)), 0)),"")

 

[Peter_SSs]

Welcome to the MrExcel board!

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

I am also not sure that you have given us the exact formula? Shouldn't this red $ sign be removed?
=IFERROR(INDEX(Source!$D$1:$D$81, MATCH(1, ($A3=(Source!$A$1:$A$81)) * ($B$1=(Source!$C$1:$C$81)) * ($B$2=(Source!$B$1:$B$81)), 0)),"")

Does anything at all get changed on the 'Chart' sheet when ...

we refresh the source data

If so, please give details.

If the data is just being refreshed on the 'Source' sheet then perhaps you could just have these formula in A3:E3 of the Chart sheet and provided where I have 1000 you use a number that will be sure to cover any amount of data you are likely to get, you wouldn't need to update anything on the Chart sheet.

Firstly, here is my 'Source' sheet (only goes to row 29)

valerieann.xlsm
	A	B	C	D
1	Date	Store	Fruit	Price
2	8/01/2023	Kroger	Apples	1
3	8/02/2023	Kroger	Apples	1
4	8/03/2023	Kroger	Apples	1
5	8/04/2023	Kroger	Apples	1.5
6	8/05/2023	Kroger	Apples	1.5
7	8/01/2023	Kroger	Oranges	1
8	8/02/2023	Kroger	Oranges	1
9	8/03/2023	Kroger	Oranges	1
10	8/04/2023	Kroger	Oranges	1.5
11	8/05/2023	Kroger	Oranges	1.5
12	8/01/2023	Kroger	Bananas	0.5
13	8/02/2023	Kroger	Bananas	0.5
14	8/03/2023	Kroger	Bananas	0.5
15	8/04/2023	Kroger	Bananas	0.3
16	8/05/2023	Kroger	Bananas	0.3
17	8/01/2023	Kroger	Strawberries	5
18	8/02/2023	Kroger	Strawberries	5
19	8/03/2023	Kroger	Strawberries	5
20	8/04/2023	Kroger	Strawberries	4
21	8/05/2023	Kroger	Strawberries	4
22	8/01/2023	Walmart	Apples	1.5
23	8/02/2023	Walmart	Apples	1.5
24	8/03/2023	Walmart	Apples	1.5
25	8/04/2023	Walmart	Apples	1.5
26	8/05/2023	Walmart	Apples	1.5
27	8/01/2023	Walmart	Oranges	2
28	8/02/2023	Walmart	Oranges	2
29	8/03/2023	Walmart	Oranges	2
30
Source

Then 'Chart' with formulas.

valerieann.xlsm
	A	B	C	D	E	F
1		Apples
2		Kroger	Walmart	Target	Aldi
3	8/01/2023	1	1.5
4	8/02/2023	1	1.5
5	8/03/2023	1	1.5
6	8/04/2023	1.5	1.5
7	8/05/2023	1.5	1.5
Chart

Cell Formulas
Range	Formula
A3:A7	A3	=LET(d,Source!A2:A1000,SORT(UNIQUE(FILTER(d,d<>""))))
B3:E7	B3	=LET(r,Source!$A2:$D1000,BYROW($A3#,LAMBDA(rw,FILTER(INDEX(r,0,4),(INDEX(r,0,1)=rw)*(INDEX(r,0,3)=$B$1)*(INDEX(r,0,2)=B2),""))))
Dynamic array formulas.