Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Thrava
 
Posts: n/a
Default two D addressing? lookup?

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
  #2   Report Post  
Frank Kabel
 
Posts: n/a
Default

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Thrava" schrieb im Newsbeitrag
...
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


  #3   Report Post  
Thrava
 
Posts: n/a
Default

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



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

--
Regards
Frank Kabel
Frankfurt, Germany

"Thrava" schrieb im

Newsbeitrag
...
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


.

  #4   Report Post  
Thrava
 
Posts: n/a
Default

Hi Frank,

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

any idea as to what's wrong?
Thanks again


-----Original Message-----
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
.

  #5   Report Post  
Frank Kabel
 
Posts: n/a
Default

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Thrava" schrieb im Newsbeitrag
...
Hi Frank,

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

any idea as to what's wrong?
Thanks again


-----Original Message-----
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
.




  #6   Report Post  
Thrava
 
Posts: n/a
Default

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

See you online ;)

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Thrava" schrieb im

Newsbeitrag
...
Hi Frank,

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

any idea as to what's wrong?
Thanks again


-----Original Message-----
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
.


.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Lookup closest number in list Jeff Excel Discussion (Misc queries) 6 November 26th 04 08:27 PM


All times are GMT +1. The time now is 09:16 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"