|
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 |
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 |
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 . |
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 . |
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 . |
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 . . |
| All times are GMT +1. The time now is 11:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com