Link to home
Start Free TrialLog in
Avatar of Tom M
Tom M

asked on

Range match

I would like a formula for column V that lookups M5 in column C and when the two-match pulls the number in Column D associated with that customer ID.  That number is then associated with ranges in i2:j5 giving me the rate in V that is in listed in k2:k4

I have manually entered my results in V to demonstratetest.xlsx

ASKER CERTIFIED SOLUTION
Avatar of Tom Farrar
Tom Farrar
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Alternative using INDEX and MATCH rather than VLOOKUP:

=INDEX($K$2:$K$4,(MATCH(INDEX(D:D,MATCH(M5,C:C,0)),$I$2:$I$4,1)))

To account for errors, it can be wrapped in an IFERROR statement:

=IFERROR(INDEX($K$2:$K$4,(MATCH(INDEX(D:D,MATCH(M5,C:C,0)),$I$2:$I$4,1))),"Check")