How to perform Index-match with great amount of rows (formula or VBA)

[ffariagui]

Hi everyone. I hope y're all fine.
I have a spreedsheet with prices for many products, according with the 4 criteria bellow:

(1) City (2) Seller (3) Date (4) Product type

Each product has a product "below" which price must be lower. So for example, the product B must be more expensive than product A, considering same City, Seller and Date. Basically i have to compare prices between products B and A to see if this rule is being obeyed. I'm trying to perform an index-match to fill a new column (Column J) so i can compare the prices, and do some calculation over it.
With worksheet formulas this task become very inefficient when the spreadsheet exceeds 500k rows
I also tried to use arrays in VBA (code below) to fill, but it also proved to be inefficient.

Do you guys have some tips on this problem?


Array_code:

Sub IndexMatch()

Dim arr As Variant
Dim i As Long
Dim j As Long
Dim StartTime As Single

StartTime = Timer

arr = Range("C4").CurrentRegion

For i = (LBound(arr, 1) + 1) To UBound(arr, 1)

For j = (LBound(arr, 1) + 1) To UBound(arr, 1)

If arr(j, 10) = arr(i, 11) Then
arr(i, 8) = arr(j, 5)
Exit For

End If
Next j
Next i


MsgBox (Timer - StartTime)

Price Calculation.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2								Formulas:	#N/D	#N/D			#N/D
3
4			City	Seller	Date	Product	Price	.	Product Below	Price Product Below	.	Key_1	Key_2				Product	Product Below
5			City1	Seller1	31/03/2022	A	194		None	None		City1Seller144651A	City1Seller144651-				A	None
6			City1	Seller1	31/03/2022	B	190		A	194		City1Seller144651B	City1Seller144651A				B	A
7			City1	Seller1	31/03/2022	C	169		A	194		City1Seller144651C	City1Seller144651A				C	A
8			City1	Seller1	31/03/2022	D	161		C	169		City1Seller144651D	City1Seller144651C				D	C
9			City1	Seller1	31/03/2022	E	199		C	169		City1Seller144651E	City1Seller144651C				E	C
10			City1	Seller1	31/03/2022	F	158		D	161		City1Seller144651F	City1Seller144651D				F	D
11			City1	Seller1	31/03/2022	G	188		D	161		City1Seller144651G	City1Seller144651D				G	D
12			City1	Seller1	31/03/2022	H	110		F	158		City1Seller144651H	City1Seller144651F				H	F
13			City1	Seller1	31/03/2022	I	157		G	188		City1Seller144651I	City1Seller144651G				I	G
14			City1	Seller1	31/03/2022	J	121		G	188		City1Seller144651J	City1Seller144651G				J	G
15			City1	Seller1	31/03/2022	K	163		G	188		City1Seller144651K	City1Seller144651G				K	G
16			City1	Seller1	31/03/2022	L	131		I	157		City1Seller144651L	City1Seller144651I				L	I
17			City1	Seller1	31/03/2022	M	106		J	121		City1Seller144651M	City1Seller144651J				M	J
18			City1	Seller1	31/03/2022	N	128		J	121		City1Seller144651N	City1Seller144651J				N	J
19			City1	Seller1	31/03/2022	O	198		L	131		City1Seller144651O	City1Seller144651L				O	L
20			City1	Seller1	31/03/2022	P	189		N	128		City1Seller144651P	City1Seller144651N				P	N
21			City1	Seller1	31/03/2022	Q	191		M	106		City1Seller144651Q	City1Seller144651M				Q	M
22			City1	Seller1	31/03/2022	R	152		L	131		City1Seller144651R	City1Seller144651L				R	L
23			City1	Seller1	31/03/2022	S	125		L	131		City1Seller144651S	City1Seller144651L				S	L
24			City1	Seller1	31/03/2022	T	189		Q	191		City1Seller144651T	City1Seller144651Q				T	Q
25			City1	Seller1	31/03/2022	U	157		Q	191		City1Seller144651U	City1Seller144651Q				U	Q
26			City1	Seller1	31/03/2022	V	116		U	157		City1Seller144651V	City1Seller144651U				V	U
27			City1	Seller1	31/03/2022	W	116		Q	191		City1Seller144651W	City1Seller144651Q				W	Q
28			City1	Seller1	31/03/2022	X	111		V	116		City1Seller144651X	City1Seller144651V				X	V
29			City1	Seller1	31/03/2022	Y	174		X	111		City1Seller144651Y	City1Seller144651X				Y	X
30			City1	Seller1	31/03/2022	Z	153		X	111		City1Seller144651Z	City1Seller144651X				Z	X
Calculation

Cell Formulas
Range	Formula
I2	I2	=INDEX($R$5:$R$30,MATCH(F3,$Q$5:$Q$30,0))
J2	J2	=IF(I2="None","None",INDEX(G:G,MATCH(M2,L:L,0)))
L2	L2	=$C3&$D3&$E3&$F3
M2	M2	=$C3&$D3&$E3&$I2

 

[offthelip]

Have a look a scripting dictionaries it might be faster to use them rather than a double loop through a large array.