0

I am running a multiple criteria INDEX+Match, and I get N/A or it goes to the last value. I have tried to reorganize the lookup array, and it didn't work. When I try the MATCH separately, it does return the right values. The problem is when I join them.

I have a list of revenue tiers and I need to know which tier a forecast falls into. Each team has different tiers. That is the other MATCH that I am trying to add to the INDEX function.

This is what I'm trying at the moment:

=MATCH(1,(B2=Sheet2!$E$4:$E$32)*(A2=Sheet2!$B$4:$B$32),0) RESULT: N/A
=MATCH(1,(B2=Sheet2!$E$4:$E$32)*(A2=Sheet2!$B$4:$B$32),1) RESULT: 29 (last value)
=MATCH(1,(B2=Sheet2!$E$4:$E$32)*(A2=Sheet2!$B$4:$B$32),-1) RESULT: N/A

I have rearranged it and it does not give me any different results, unfortunately.

I can attach a file with the data, if needed.

Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
literarymanchester
  • 23
  • 3
  • 2
    What version do you have? – Scott Craner Apr 26 '23 at 15:22
  • 1
    Also see if this answer helps: https://stackoverflow.com/questions/42492758/vlookup-using-2-columns-to-reference-another/42493697#42493697 – Scott Craner Apr 26 '23 at 15:23
  • Wrap each match() in iferror(), with 0 as the result and then use max(0 to find the highest value. So that would be Max(0,29,0) which will return 29. – Solar Mike Apr 26 '23 at 15:23
  • 1
    My guess is that you are using an older version than office 365 and you need to array enter the first formula using Ctrl-Shift-Enter instead of Enter when exiting edit mode. And if that is not the case then you do not have any that match both criteria. – Scott Craner Apr 26 '23 at 15:24
  • I am running Version 2208. – literarymanchester Apr 26 '23 at 15:35
  • 1
    Then there is a good chance that none of your data meets both criteria. You can easily test it by putting both `=(B2=Sheet2!$E$4:$E$32)` and `=(A2=Sheet2!$B$4:$B$32)` side by side in blank columns and it will spill the results. You can then see if any row has `TRUE` in **BOTH** columns. – Scott Craner Apr 26 '23 at 15:38
  • Could you share the screenshots of your worksheets? Also, you are playing around with the 3rd MATCH parameter i.e. what kind of match do you want to return? – VBasic2008 Apr 26 '23 at 15:49
  • The problem is coming from the revenue column. I think the match formula is looking for an exact match, and I need it to be a less than/greater than type of match, combined with the other criteria. Is that possible? – literarymanchester Apr 26 '23 at 15:51
  • 2
    Yes, it is looking for an exact match. I think it time you mock up a minimal example of the data, inputs and expected output. – Scott Craner Apr 26 '23 at 15:55
  • Two relevant screenshots: Forecasts: https://ibb.co/7GKdcjn Revenue tiers: https://ibb.co/c1xdLRP – literarymanchester Apr 26 '23 at 15:56
  • 1
    You cannot use this special kind of `MATCH` successfully with approx. matches: it is multiplying `TRUEs` and `FALSEs` and returns the first row or column index where all conditions are met (`TRUE` or 1) or an error. You could add those screenshots to your post and additionally, you should share the expected results for the given screenshots. – VBasic2008 Apr 26 '23 at 16:15
  • 2
    Please [edit] the post to include all of the relevant information from the comments. – Scott Craner Apr 26 '23 at 16:25
  • 2
    try `=MATCH(1,(B2>=Sheet2!$E$4:$E$32)*(A2=Sheet2!$B$4:$B$32),0)` – Scott Craner Apr 26 '23 at 16:27
  • 1
    This one worked! Thank you so much! – literarymanchester Apr 26 '23 at 17:58

0 Answers0