two D addressing? lookup?

[Thrava]

Hi everyone, I hope someone can help with this.

A simple sample of what I like to do is this.

A B C D E F G H I

1 60% 70% 80% 90% 74% 1.6% ?

2 1% $10 $20 $30 $50


3 5% $40 $50 $60 $70

Range B1:E1 is the sales person's market share at the end
of the month. Range A2:A3, say it his/ her change in
market share from previous month. I like to be able to
pay this person base on his/ her performance in cells G1
and H1. This person ended the month with 74% market share
and an increase of 1.6% from previous months.
What formula in I1 would "look" at G1 and I1 and find the
closest match in the matrix? In this case it would be $20.

I know how to use Vlookup, Hlookup, but this is a two
dimensional "addressing"

I hope someone has the answer

 

[Frank Kabel]

Hi
=INDEX(A1:E3,MATCH(H1,A1:A3,0),MATCH(G1,A1:E1,0))

 

[Thrava]

Frank,
You're incredible. I've been seeing your posts online.
Thank you so much.

 

[Thrava]

Hi Frank,

I tried your suggestion. But it gives me #N/A error.

any idea as to what's wrong?
Thanks again

 

[Frank Kabel]

Hi
this means that there's no eact match. Try:
=INDEX(A1:E3,MATCH(H1,A1:A3,1),MATCH(G1,A1:E1,1))

 

[Thrava]

Frank,
this worked.
Thank you again for getting back to me.

See you online