Excel – Fix INDEX(MATCH()) Function Producing Zero

microsoft excelworksheet-function

I have an INDEX(MATCH()) function that is structured as follows:

=INDEX(INDIRECT(HLOOKUP(P13,Price,2)),MATCH(1,((INDIRECT(HLOOKUP(P13,EffDate,2))>=N13))*((INDIRECT(HLOOKUP(P13,Index,2))<=N13)),1))

enter image description here

To break it down, I have INDEX() referencing a range to output a price value, with a MATCH() criteria for a single cell input (a date) to evaluate which row in the referenced table qualifies based upon a start and end date. For both INDEX() and MATCH(), INDIRECT(HLOOKUP()) is being used to reference the correct range (out of four, on a separate sheet).

enter image description here

For whatever reason, the output is coming out as 0. I've tried using both text (string) as well as numeric (Excel date numeric value) formatting, but the results appear to be the same. Any idea what I'm doing wrong here? Thanks!

Best Answer

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:

  1. 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.

  2. Use an IF test in the lookups. Might be hard to implement without ending up where you are now.

  3. Build the lookup ranges dynamically. There's a lot to go with here. Definitely the best choice so far.

  4. 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.

Related Question