I have come up with the following formula and would like to get an explanation about the ROW data in the filter formula and the 9^99 at the beginning of vlookup.
=VLOOKUP(9^99,FILTER({ROW(L:L),L:L},L:L>=Q1,L:L<(Q1 1), D:D=R1),2)
CodePudding user response：
The first parameter of VLOOKUP is the Search key, and the caret (^) next to 9 is an indication that the next number will be its exponents. So basically it is 9⁹⁹ which is equivalent to 2.951266543 E 94.
Reference:
CodePudding user response：
The is_sorted parameter of your VLOOKUP is omitted, which is the equivalent of setting it to TRUE.
From https://support.google.com/docs/answer/3093318:
If is_sorted is TRUE or omitted, the nearest match (less than or equal to the search key) is returned. If all values in the search column are greater than the search key, #N/A is returned.
CodePudding user response：
your vlookup formula does not include 4th parameter which is automatically set to 1 (TRUE) if not present so the vlookup formula works in approximate mode instead of the exact mode. this means that vlookup tries to find exact or closest value to 9^99 from the filter.
=VLOOKUP(9^99, FILTER({ROW(L:L), L:L}, L:L>=Q1, L:L<(Q1 1), D:D=R1), 2)
9^99 is at this point just a big number and it doesn't matter if its 8^789 or 5^555 or just 200000 or 45676445443541748 - it could be any big number you wish it just needs to be larger than total number of rows in your sheet