Index match with range of values

[Brian65]

I have three columns, and the formula should look at the division that is input and then look at the dollar amount input and then output should be what level of approval should sign off on dollar amount
I enter ABC and $100,000 = AVP (Sucess)
I enter ABC and $5 = #N/A (Fail)

I would like $0 to $99,999 = Not Required
$100,000 to $499,999 = AVP
And so on
COL A COL B Col C
Division Amount Approval
ABC $0 Not Required
ABC $100,000 AVP (4th level)
ABC $500,000 VP (5th level)
ABC $1,000,000 SVP/ Segment Head
XYZ $0 Not Required
XYZ $200,000 AVP (4th level)
XYZ $500,000 VP (5th level)
XYZ $1,000,000 SVP/ Segment Head

{=INDEX(C3:C10,MATCH(1,(A14=A3:A10)*(A15=B3:B10),0))}

Thank you in advance

[humdingaling]

Please Login or Register  to view this content.

[Phuocam]

Or try:

=LOOKUP(2,1/(A3:A10=A14)/(B3:B10<=A15),C3:C10)

[Brian65]

Thank you both very much! I've learned something new today