Index Match with mutliple criteria

[FunctionalHippie]

I'm working on a formula to pull a "Fee" from a fee schedule based on user-input "Insurance Company" and "GCA".

So far, I've tried dozens of syntax options for a few hours today and this is the closest I can get "{=INDEX(D2:D31,MATCH(F6,A2:A31,0),MATCH(G6,B2:B31,-1),MATCH(G6,C2:C31,1))}" but it is showing errors too.

I'm trying to...

Find F6 in column A2:A31
Find the number in B2:B31 that is less than G6
Find the number in C2:C31 that is greater than G6
Return the corresponding cell's value from D2:D31

Can anyone get me closer?

I have lot's of crazy formulas and do lot's of weird stuff, so I'll be happy to help others where I can now that I'm a member...unfortunately, right now I'm the one who is stuck!

Thanks in advance, Stephen

[NBVC]

Try:

=SUMIFS(D2:D31,A2:A31,F6,B2:B31,"<="&G6,C2:C31,">="&G6)

[FunctionalHippie]

Perfect. That's a total different direction than I was looking! Thank you NBVC!

[NBVC]

It works because you are dealing with numeric results.... if you had text results, then we would need to use Index/Match array formula.