0

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!

4
  • 1
    P13 and N13 are blank in the screenshot. Also you have a range/table called Index which may cause problems with the INDEX function (not sure about that though). Jan 23 at 11:41
  • @DarrenBartrup-Cook Actually, they are referring to a different sheet that isn't shown in the screenshots, but I give you my word they are not blank. P13 gives the division code that tells Excel which range to look in (between EXW, GA, IL, and PA), and N13 gives a date, which searches for the correct row in the ranges.
    – Arctiic
    Jan 23 at 21:10
  • I'm not sure what was causing the issue in this specific case, but I did manage to get it working on my own after rereading (for like the 20th time) how Index Match works, and then combining all four ranges into a single range with the DIV code as a entry column. I'm not sure what else I changed, but the working formula in the end was: =IF(N13="","",(INDEX(rng_2,MATCH(1,(P13=rng_1)*(N13>=rng_3)*(N13<=rng_4),0))))
    – Arctiic
    Jan 23 at 21:12
  • Just out of academic curiosity, I'd still be interested in seeing what was causing the initial issue as written out in OP, though. If anyone has any ideas, feel free to answer!
    – Arctiic
    Jan 23 at 21:14

1 Answer 1

2

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.

New contributor
Jeorje is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
1
  • Thank you for the excellent answer, it was both thorough and insightful! The intricacies involving MATCH() and it's match-type parameters and mechanics had eluded me despite repeatedly reading through the official resources (as well as unofficial ones). As you surmised, I started off without a complete conceptual understanding of INDEX(MATCH())'s intended usage, hence my fragmented approach of trying to jigsaw together all the isolated process steps using what functions I was familiar with. I've gained a more clear understanding of these functions, which is more than I had hoped for!
    – Arctiic
    Jan 25 at 3:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.