VBA Using Arrays for lookup

[dodo1051]

Hi guys,

so i have a set of data with 600k rows, and i was using index-match to lookup value but its really slow, so i'm starting to look into vba,
but, i still dont quite understand the concept of two dimensional arrays, am i able to reference a column with a header name (like python)?
and is the data structured per row?

for example, how do i fill value for tbl_result.ListColumns("Price").Range
providing data from tbl_ref?

[TABLE="class: outer_border, width: 192"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 2"]tbl_result[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Fruits[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Apple[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Pinapple[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Grapes[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Pear[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]Orange[/TD]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: outer_border, width: 236"]
<tbody>[TR]
[TD][/TD]
[TD]tbl_ref[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Fruits[/TD]
[TD]Loc[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Apple[/TD]
[TD]CN[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Pinapple[/TD]
[TD]INDO[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Grapes[/TD]
[TD]SING[/TD]
[TD="align: right"]320[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Pear[/TD]
[TD]TW[/TD]
[TD="align: right"]110[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]Orange[/TD]
[TD]TH[/TD]
[TD="align: right"]550[/TD]
[/TR]
</tbody>[/TABLE]

 

[offthelip]

Have a look at this thread that shows you how to do a Vlookup using variant arrays:
https://www.mrexcel.com/forum/excel-questions/1043185-vlookup-vba-alternative.html