How to optimize Index Match for huge dataset

Sort the table by the top row and leftmost column, then use a range lookup (change the last MATCH argument to 1).

What was the lookup value? What were the values in the first row of the lookup table? Were they sorted left to right in ascending order or descending order? What column number did the MATCH() function return (use the Evaluate Formula tool: https://support.office.com/en-us/art...6-a70aa409b8a7 )? What column number should it have returned?

I am not sure I understand the question. Your lookup table (F2:I7) is set up exactly as shg suggested with the left column (column F) sorted in ascending order and the first row (G2:I2) also sorted in ascending order. Recognizing that these are dummy values, perhaps they are not sorted in your original lookup table, but I assume you are able to sort the table like your sample file.

With column F and row 2 sorted in ascending order, as shg suggested, it should be a simple matter to replace the 0's in each MATCH() function with 1's I edited the formula in C3 to be

Formula:

Please Login or Register  to view this content.

(note the additional change to the absolute referencing for the C2 lookup value reference in the 2nd MATCH() function) and it seemed to work just fine. What about your sample file does not accurately represent your true file? What questions do you have about this kind of lookup?

If columns are in the same order then
B3
=INDEX(G$3:G$7,MATCH($A3,$F$3:$F$7,1))
But if the number on table 1 might not exist on table 2, it still give result from big number that less than lookup value.

or try
=LOOKUP(1,1/($F$3:$F$7=$A3),G$3:G$7)