0

I have a workbook that has a sheet the summarizes all of the other sheets. In a couple of columns (4), I need to find a value that matches a unique set of values in another sheet.

All well and good, except I need to match where things are between a range of indexes.

Here's what I'd LIKE to do, but it appears you can't sue >= <= operators in an index match:

=INDEX(Rebates[Group],

MATCH(1,
([@[Product Index]]<=Rebates[End Product Index]) *
([@[Product Index]]>=Rebates[Start Product Index]) *

([@[Scenario Index]]<= Rebates[End Scenario Index]) *
([@[Scenario Index]]>= Rebates[Start Scenario Index]) *

([@[Segment Index]] <= Rebates[End Segment Index])*
([@[Segment Index]] >= Rebates[Start Segment Index]),0))

Is there a way to do this?

Edit: Group will return a string value

Chris Leonard
  • 13
  • 3
  • Are the values in `Rebates[Group]` numbers or text? [This](https://stackoverflow.com/questions/42492758/vlookup-using-2-columns-to-reference-another) is probably going to be helpful. – BigBen Jul 01 '20 at 20:48
  • They're strings. – Chris Leonard Jul 01 '20 at 21:19
  • If you have access to `FILTER`, then use that.. otherwise see the "array formula" section of the linked question. – BigBen Jul 01 '20 at 21:30
  • Did you use Ctrl-Shift-Enter instead of Enter to force it into an array formula when exiting edit mode? – Scott Craner Jul 01 '20 at 22:17
  • @ScottCraner - This fixed it! I have some master Data issues, but this got me 90% of the results I needed. Thanks to both of you – Chris Leonard Jul 01 '20 at 22:31

0 Answers0