Hi all, I need a little help please. I have data displayed like this:
a1 g7 k11
b2 h8 l12
c3 i9 m13
d4 j10 n14
e5 o15
f6 p16
and I can pull i can pull the highest value with the max function, but I also want to be able to pull the value next to the highest value. so when the max, in this case 16, is pulled I want P to pull up in another cell. i tried an index match but it seems to only work with 1 dimensional arrays.
I hope this makes sense. thank you for your help
Brian
Last edited by bmhalula; 04-25-2011 at 09:16 AM.
Hi Brian,
Not sure I understand your request completely or the ranges involved, but maybe this is a start.
=INDEX(B1:B10,MATCH(MAX(A1:A10),A1:A10,0))
column B holds the text to pull and column a has the values in which we look for the max
HTH
Regards, Jeff
Yeah I probably didn't explain it too well. There are a total of 6 columns, 1, 3 and 5 are sales reps names and 2, 4, and 6 are their sales for the previous day. the max value could be in any of the columns 2, 4, or 6. i need to pull the corresponding sales reps name with the highest sales. thanks.
Sorry Brian, not so sure how to do this, but maybe if you attach a sample workbook somebody can undoubtedly help.
Okay, it looks like you have organized your spreadsheet to complicate things. You have three sales reps and want to match the rep's name with the highest sales for the previous day. I set it up as follows:
col a col b
row 1Rep Sales
row 2 RepA $1,000
row 3 RepB $1,100
row 4 RepC $1,200
row 5
row 6Rep MAX
row 7 RepC $1,200 [values will be returned by the formulae below]
In cell b7 (under MAX) put the formula: =MAX(B2:B4)
In cell a7 (under Rep) put the formula: =INDEX(A2:B4,MATCH(B7,B2:B4,FALSE),1)
the b7 formula will return "$1,200" and the a7 formula will return "RepC"
Hope this helps.
Originally Posted by bmhalula
Ok here is a sample of how my data is laid out. i just care about "Yesterday" Sales not the monthly sales. at the bottom i have Rep and Sales. Below the Sales cell is the MAX for all of the yesterday columns. In the Rep column I need to be able to pull the corresponding rep, in this case Matt. thanks for your help
I quite honestly don't know how to come up with a formula that crosses matrices.
Take a look at the attached sample workbook with a workable solution if you can restructure your data.
Edit: Missed this one...For the max you can know just apply =MAX(C2:C17)
Another way:
Please Login or Register to view this content.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Thanks NBVC.
I had a feeling Choose was the method to use, but just couldn't get the syntax right.
thanks NBVC. I also found another way. seems a little more convoluted than yours but it does the trick.
=IF(MAX(J145:J150)=I152,INDEX($I$145:$I$150,MATCH(I152,$J$145:$J$150,0)),IF(MAX(M145:M148)=I152,INDEX($L$145:$L$148,MATCH(I152,$M$145:$M$148,0)),IF(MAX(P145:P150)=I152,INDEX($O$145:$O$150,MATCH(I152,$P$145:$P$150,0)))))
NBVC, can you please explain the Rept function and why the "z", for 255 times? thanks
REPT("z",255) produces a string of 255 "z's" as you have noted. LOOKUP() looks for the last entry in an array that is is less than or equal to the lookup value (here z, 255 times). It is highly unlikely that you will have a string of 255 z's as an entry, so LOOKUP returns the last text entry in the range, since it would be the last time an entry is less than the lookup value.
The CHOOSE({1,2,3}) allows you to get an array of 3 results (each of the INDEX/MATCH functions that follow). Then Lookup looks for the last text entry in that array. #N/A error from any of the INDEX/MATCHes are ignored. So if 2 of the INDEX/MATCH functions give #N/A results and one gives an actual result, that one will be "chosen" and returned as the final result.... You can add a 4th CHOOSE parameter to return a blank if all the INDEX/MATCH functions return #N/A.
Please Login or Register to view this content.
A B C*
**ECI111* **ECI059* 230
**ECI510* **ECI514 206
**ECI511* **ECI061* 674
**ECI512* **ECI111* 10
**ECI513* **ECI077 4
**ECI514* **ECI078 32
ECI109 ECI510 20
i want to arrange 2nd & 3rd column (B,C)qty will change with col(B) like column (A)
munna Welcome to the Forum.
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks