Dynamic INDEX MATCH row & column

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Lookup assumes the data is sorted in ascending order.
It doesn't do anything to 'verify' if the data is actually sorted or not. It just flat assumes and proceeds as if it is.
So lookup will find the largest value that is less than or equal to the lookup value.
Normally, because it's assumed to be sorted ascending, lookup continues looking to the right until it finds a value larger than the lookup value.
And then returns the previous one as the largest value that is less then or equal to the lookup value.

By using the bignum, we are guaranteed that the lookup value will be greater than or equal to any possible value in the range.
So it keeps looking to the right for a larger value.
When it can't find any a value 'greater than' the bignum, it then assumes that the last value IS the largest value less than or equal to the lookup value.
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Watch MrExcel Video

Forum statistics

Threads
1,089,761
Messages
5,410,271
Members
403,305
Latest member
tray2014

This Week's Hot Topics

Top