Hi all,

I have a working formula but I now want to use it to search across a large dataset so I need to optimize the formula.
I am trying to use index match to look for value in table 2.
But my current dataset has 0.5m rows which it take forever to do the calculation and crashing now and again...

My index match formula: =INDEX($F$3:$I$7,MATCH($A3,$F$3:$F$7,0),MATCH($B$2,$F$2:$I$2,0))
Couldn't attached, so please find the sample data set below.
I am looking for value, value1, and value2 in Table 2 by using the Number column in table 1.

Table 1
Number value value1 value2 (index match for the value, value1, value2)
5
3
2
1
4

Table 2
number value value1 value2
1 asfd 2341 a23roij
2 asdf 32q54 qwejfkds
3 adsfb 34qfrv 23frd
4 dfew 45twgefs 23ew
5 qewrf 34f 23eww


Is there any way this kind of search can be run on this scale?

Thank you.