For academic curiosity, and very practical concern, I believe the problem is in your use of the MATCH
function, specifically the match type.
You specify "1" which is to match the... well, it gets complicated in a non-sorted list it is applied to... in this case, the last value less than the value to be matched. "Last" definitely not meaning the last one before the first matching value, rather the very last one in the list to match from.
Your lookups produce arrays inside the formula. (And as separable results, now that we have SPILL tech.) And these are the values one wants to look at... PLUS some empty rows below them.
Those empty rows present "0" in the internal arrays the formula is using, and given the nature of the data, unless addressed particularly, the last element in those arrays will be a 0. So that last element will be the one selected.
Hence, your INDEX
is presented a demand for the 9th row of the range it is given and that contains no data, in what's shown to us, so it returns a "0" as blank cells return "0" in this use.
If you address that, I believe your formula will work. Naturally, that is sight unseen judgment, but assuming everything is there. One might, for example:
Fill in the last element of each possible range (row 14 for all columns). Risky, since most filling might lead to always getting that row.
Use an IF
test in the lookups. Might be hard to implement without ending up where you are now.
Build the lookup ranges dynamically. There's a lot to go with here. Definitely the best choice so far.
Stop using INDIRECT
, rather, use INDEX
instead. Dynamically selecting only the populated data regions would become easier as you directly pick them, and like in 3. (above), would probably use COUNT
to pick how many rows to be in the data picked amongst.
(Oddly, the first thing jumping to mind with INDEX
and a complex construction for its attending MATCH
is the fact that while a row was specified, no column was addressed in any way. By that, I mean usually even if not planning to put anything at all in that parameter's place, one STILL needs the comma to avoid an error. Yet in this formula, one doesn't, which in addition to being odd, is also interesting. After settling that in my head, I (only) then realized that if it had been a problem here, you'd've been getting an error, not a 0... so it was a dead end either way.)
The chief value to using INDEX
to address things is the easy, natural way of working with it to produce columns you need and limited to only those with data. That assumes well-formed data like your pics show, and it also assumes you feel natural inside INDEX
. If you feel clearer working on creating dynamic strings, or like the isolation of steps as it separates different tasks and leads to perhaps an easier understanding of, and perhaps easier improvement-over-time of, the spreadsheet, then by all means stick with INDIRECT
, but apply the dynamicism needed to the ranges whose strings you build. In no way would you catch me suggesting a modern computer has the least performance impact from using (...hush, the demon is mentioned...) "volatile" functions. Do what you find works the best for you in building and maintaining your spreadsheet. But do be aware there are options.
P13
andN13
are blank in the screenshot. Also you have a range/table calledIndex
which may cause problems with theINDEX
function (not sure about that though).=IF(N13="","",(INDEX(rng_2,MATCH(1,(P13=rng_1)*(N13>=rng_3)*(N13<=rng_4),0))))