| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index/Match problem
Hello,
I have a pivot table with the following data: Apples $1,000 Bananas $1,000 Pears $1,000 Oranges $500 Peaches $450 Using the "LARGE" formula, I am pulling the 3 largest values of $1,000. I need to match the name to the value, but my formula is duplicating the first name 3 times so it looks like this: apples $1,000 apples $1,000 apples $1,000 How do I get the formula to recognize there are 3 different names? My formula: =INDEX($C$13:$C$65536,MATCH($E$10,$F$13:$F$65536,0 )) Any help is greatly appreciated!! Thanks! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index/Match problem
AutoFilter Select Top 10 and change it Top 3 OK
"Lisa" wrote: Hello, I have a pivot table with the following data: Apples $1,000 Bananas $1,000 Pears $1,000 Oranges $500 Peaches $450 Using the "LARGE" formula, I am pulling the 3 largest values of $1,000. I need to match the name to the value, but my formula is duplicating the first name 3 times so it looks like this: apples $1,000 apples $1,000 apples $1,000 How do I get the formula to recognize there are 3 different names? My formula: =INDEX($C$13:$C$65536,MATCH($E$10,$F$13:$F$65536,0 )) Any help is greatly appreciated!! Thanks! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index/Match problem
one thing I have done in this situation is to add some helper columns with
=B1+row()/1000 copy down (value selected to insure no overlap depending on number of variables and minimum incremental difference) use a second helper column with =rank(C1,C:C,1) copy down use index(A:A,Match(1,D:D) and index(B:B,Match(1,D:D) the same for 2, 3 4 etc. You can use other secondary tie breakers depending on what you want to do. I have used multiple levels of tie breakers. You can, of course, make the make the equations more fancy and by changing order of columns to put the ranking formula in the first column, use Vlookup() instead of index match "Lisa" wrote: Hello, I have a pivot table with the following data: Apples $1,000 Bananas $1,000 Pears $1,000 Oranges $500 Peaches $450 Using the "LARGE" formula, I am pulling the 3 largest values of $1,000. I need to match the name to the value, but my formula is duplicating the first name 3 times so it looks like this: apples $1,000 apples $1,000 apples $1,000 How do I get the formula to recognize there are 3 different names? My formula: =INDEX($C$13:$C$65536,MATCH($E$10,$F$13:$F$65536,0 )) Any help is greatly appreciated!! Thanks! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index/Match problem
Yes, I realize I can filter and sort the data within the table, but I need to
extract the largest values from the table and then the names associated with them. In my case, because the 3 largest values are all the same, I am getting only one name repeated 3 times. I don't know how to get it to differentiate the names - possibly use in IF statement within the formula? "Teethless mama" wrote: AutoFilter Select Top 10 and change it Top 3 OK "Lisa" wrote: Hello, I have a pivot table with the following data: Apples $1,000 Bananas $1,000 Pears $1,000 Oranges $500 Peaches $450 Using the "LARGE" formula, I am pulling the 3 largest values of $1,000. I need to match the name to the value, but my formula is duplicating the first name 3 times so it looks like this: apples $1,000 apples $1,000 apples $1,000 How do I get the formula to recognize there are 3 different names? My formula: =INDEX($C$13:$C$65536,MATCH($E$10,$F$13:$F$65536,0 )) Any help is greatly appreciated!! Thanks! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index/Match problem
I'm thinking you'd have to put in a helper column with something like
=COUNTIF($A$2:A2,A2) (to check row 2). "Lisa" wrote: Hello, I have a pivot table with the following data: Apples $1,000 Bananas $1,000 Pears $1,000 Oranges $500 Peaches $450 Using the "LARGE" formula, I am pulling the 3 largest values of $1,000. I need to match the name to the value, but my formula is duplicating the first name 3 times so it looks like this: apples $1,000 apples $1,000 apples $1,000 How do I get the formula to recognize there are 3 different names? My formula: =INDEX($C$13:$C$65536,MATCH($E$10,$F$13:$F$65536,0 )) Any help is greatly appreciated!! Thanks! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index/Match problem
It appears that your table is already sorted on the number value.
My formula: =INDEX($C$13:$C$65536,MATCH($E$10,$F$13:$F$65536,0 )) I'm assuming E10 is your LARGE formula that returns 1000. Is your range of data really all the way to row 65536? =IF(ROWS($1:1)<=COUNTIF(F$13:F$17,E$10),INDEX(C$13 :C$17,MATCH(E$10,F$13:F$17,0)+ROWS($1:1)-1),"") Copy down until you get blanks. If you want a top n list then it gets pretty complicated when there are ties involved. Biff "Lisa" wrote in message ... Yes, I realize I can filter and sort the data within the table, but I need to extract the largest values from the table and then the names associated with them. In my case, because the 3 largest values are all the same, I am getting only one name repeated 3 times. I don't know how to get it to differentiate the names - possibly use in IF statement within the formula? "Teethless mama" wrote: AutoFilter Select Top 10 and change it Top 3 OK "Lisa" wrote: Hello, I have a pivot table with the following data: Apples $1,000 Bananas $1,000 Pears $1,000 Oranges $500 Peaches $450 Using the "LARGE" formula, I am pulling the 3 largest values of $1,000. I need to match the name to the value, but my formula is duplicating the first name 3 times so it looks like this: apples $1,000 apples $1,000 apples $1,000 How do I get the formula to recognize there are 3 different names? My formula: =INDEX($C$13:$C$65536,MATCH($E$10,$F$13:$F$65536,0 )) Any help is greatly appreciated!! Thanks! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index/Match problem
This works perfectly - THANK YOU SO MUCH!!!
"T. Valko" wrote: It appears that your table is already sorted on the number value. My formula: =INDEX($C$13:$C$65536,MATCH($E$10,$F$13:$F$65536,0 )) I'm assuming E10 is your LARGE formula that returns 1000. Is your range of data really all the way to row 65536? =IF(ROWS($1:1)<=COUNTIF(F$13:F$17,E$10),INDEX(C$13 :C$17,MATCH(E$10,F$13:F$17,0)+ROWS($1:1)-1),"") Copy down until you get blanks. If you want a top n list then it gets pretty complicated when there are ties involved. Biff "Lisa" wrote in message ... Yes, I realize I can filter and sort the data within the table, but I need to extract the largest values from the table and then the names associated with them. In my case, because the 3 largest values are all the same, I am getting only one name repeated 3 times. I don't know how to get it to differentiate the names - possibly use in IF statement within the formula? "Teethless mama" wrote: AutoFilter Select Top 10 and change it Top 3 OK "Lisa" wrote: Hello, I have a pivot table with the following data: Apples $1,000 Bananas $1,000 Pears $1,000 Oranges $500 Peaches $450 Using the "LARGE" formula, I am pulling the 3 largest values of $1,000. I need to match the name to the value, but my formula is duplicating the first name 3 times so it looks like this: apples $1,000 apples $1,000 apples $1,000 How do I get the formula to recognize there are 3 different names? My formula: =INDEX($C$13:$C$65536,MATCH($E$10,$F$13:$F$65536,0 )) Any help is greatly appreciated!! Thanks! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index/Match problem
You're welcome. Thanks for the feedback!
Biff "Lisa" wrote in message ... This works perfectly - THANK YOU SO MUCH!!! "T. Valko" wrote: It appears that your table is already sorted on the number value. My formula: =INDEX($C$13:$C$65536,MATCH($E$10,$F$13:$F$65536,0 )) I'm assuming E10 is your LARGE formula that returns 1000. Is your range of data really all the way to row 65536? =IF(ROWS($1:1)<=COUNTIF(F$13:F$17,E$10),INDEX(C$13 :C$17,MATCH(E$10,F$13:F$17,0)+ROWS($1:1)-1),"") Copy down until you get blanks. If you want a top n list then it gets pretty complicated when there are ties involved. Biff "Lisa" wrote in message ... Yes, I realize I can filter and sort the data within the table, but I need to extract the largest values from the table and then the names associated with them. In my case, because the 3 largest values are all the same, I am getting only one name repeated 3 times. I don't know how to get it to differentiate the names - possibly use in IF statement within the formula? "Teethless mama" wrote: AutoFilter Select Top 10 and change it Top 3 OK "Lisa" wrote: Hello, I have a pivot table with the following data: Apples $1,000 Bananas $1,000 Pears $1,000 Oranges $500 Peaches $450 Using the "LARGE" formula, I am pulling the 3 largest values of $1,000. I need to match the name to the value, but my formula is duplicating the first name 3 times so it looks like this: apples $1,000 apples $1,000 apples $1,000 How do I get the formula to recognize there are 3 different names? My formula: =INDEX($C$13:$C$65536,MATCH($E$10,$F$13:$F$65536,0 )) Any help is greatly appreciated!! Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| advanced lookup/index-match problem | Excel Worksheet Functions | |||
| INDEX / MATCH problem | Excel Worksheet Functions | |||
| Possible index/match problem? | Excel Worksheet Functions | |||
| Array index, match problem | Excel Worksheet Functions | |||
| Index Match Problem | Excel Worksheet Functions | |||
Linear Mode
