| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup / Summary Table
I want Excel to lookup a value in a table and return each row it finds that
value in. As an example: Lookup Value: Ford Lookup Table: A1: Ford B1: Focus A2: Chevy B2: Malibu A3: Chevy B3: S10 A4: Ford B4: Ranger Results Table: A1: Ford B1: Focus A2: Ford B2: Ranger What I dont want is: A1: Ford B1: Focus A2: B2: A3: B3: A4: Ford B4: Ranger In other words, I dont want a bunch of blank rows in the new summary table. What function(s) can I use to create my summary table? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup / Summary Table
How about PIVOT table?
"Joe" wrote: I want Excel to lookup a value in a table and return each row it finds that value in. As an example: Lookup Value: Ford Lookup Table: A1: Ford B1: Focus A2: Chevy B2: Malibu A3: Chevy B3: S10 A4: Ford B4: Ranger Results Table: A1: Ford B1: Focus A2: Ford B2: Ranger What I dont want is: A1: Ford B1: Focus A2: B2: A3: B3: A4: Ford B4: Ranger In other words, I dont want a bunch of blank rows in the new summary table. What function(s) can I use to create my summary table? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup / Summary Table
Hi Joe,
have you tried using AutoFilter? If you particularly need the results in a seperate table, follow this. Assuming your lookup table is in Sheet1 and your results table will be in Sheet2: Put your lookup value in Sheet2!A1, then in Sheet2!B1: =IF(COUNTIF(Sheet1!$A$1:$A$50,$A$1)=ROW(1:1), INDEX(Sheet1!$B$1:$B$50,LARGE(INDEX((Sheet1!$A$1:$ A$50=$A$1)* (ROW(Sheet1!$B$1:$B$50)-CELL("ROW",Sheet1!$B$1:$B$50)+1),), COUNTIF(Sheet1!$A$1:$A$50,$A$1)+1-ROW(1:1))),"-") in Sheet2!A2: =IF(COUNTIF(Sheet1!$A$1:$A$50,$A$1)=ROW(2:2),$A$1 ,"-") Copy Sheet2!B1 to Sheet2!B2, then copy Sheet2!A2:B2 down as far as required. "Joe" wrote in message ... I want Excel to lookup a value in a table and return each row it finds that value in. As an example: Lookup Value: Ford Lookup Table: A1: Ford B1: Focus A2: Chevy B2: Malibu A3: Chevy B3: S10 A4: Ford B4: Ranger Results Table: A1: Ford B1: Focus A2: Ford B2: Ranger What I dont want is: A1: Ford B1: Focus A2: B2: A3: B3: A4: Ford B4: Ranger In other words, I dont want a bunch of blank rows in the new summary table. What function(s) can I use to create my summary table? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup / Summary Table
Normally a pivot table would work. I didn't mention in my original post that
the data that I need to summarize will be imported into Microsoft Streets & Trips. The example I gave was an oversimplification of what I'm doing. I really have a list of locations and their corresponding longitude / latitudes that Streets & Trips will display on a map. The import feature is not very fancy and can not handle things like filters / pivot tables. Thanks for the suggestion though, it would work if I didn't have to use the data for importing. "Teethless mama" wrote: How about PIVOT table? "Joe" wrote: I want Excel to lookup a value in a table and return each row it finds that value in. As an example: Lookup Value: Ford Lookup Table: A1: Ford B1: Focus A2: Chevy B2: Malibu A3: Chevy B3: S10 A4: Ford B4: Ranger Results Table: A1: Ford B1: Focus A2: Ford B2: Ranger What I dont want is: A1: Ford B1: Focus A2: B2: A3: B3: A4: Ford B4: Ranger In other words, I dont want a bunch of blank rows in the new summary table. What function(s) can I use to create my summary table? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup / Summary Table
Steve,
Normally AutoFilter would work. The example I gave was an oversimplification of what I am doing. The summary table I am trying to create will be imported by Microsoft Streets & Trips. The data I am working with is actually a list of locations with their respective longitude / latitude. Streets & Trips can't handle filters and pivot tables. Therefore there is a need to create a new separate table to use for the import process. To add a few questions to your original answer (which worked by the way, thanks)... 1. How do I show more columns in my new summary table? 2. How do I add a second value for Excel to lookup? Once again, thanks for your help. Joe "Steve Dunn" wrote: Hi Joe, have you tried using AutoFilter? If you particularly need the results in a seperate table, follow this. Assuming your lookup table is in Sheet1 and your results table will be in Sheet2: Put your lookup value in Sheet2!A1, then in Sheet2!B1: =IF(COUNTIF(Sheet1!$A$1:$A$50,$A$1)=ROW(1:1), INDEX(Sheet1!$B$1:$B$50,LARGE(INDEX((Sheet1!$A$1:$ A$50=$A$1)* (ROW(Sheet1!$B$1:$B$50)-CELL("ROW",Sheet1!$B$1:$B$50)+1),), COUNTIF(Sheet1!$A$1:$A$50,$A$1)+1-ROW(1:1))),"-") in Sheet2!A2: =IF(COUNTIF(Sheet1!$A$1:$A$50,$A$1)=ROW(2:2),$A$1 ,"-") Copy Sheet2!B1 to Sheet2!B2, then copy Sheet2!A2:B2 down as far as required. "Joe" wrote in message ... I want Excel to lookup a value in a table and return each row it finds that value in. As an example: Lookup Value: Ford Lookup Table: A1: Ford B1: Focus A2: Chevy B2: Malibu A3: Chevy B3: S10 A4: Ford B4: Ranger Results Table: A1: Ford B1: Focus A2: Ford B2: Ranger What I dont want is: A1: Ford B1: Focus A2: B2: A3: B3: A4: Ford B4: Ranger In other words, I dont want a bunch of blank rows in the new summary table. What function(s) can I use to create my summary table? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup / Summary Table
Ok here goes...
(Put your list of criteria in Sheet2!Z1:Z10 for this example - obviously you can change that reference to whatever you need.) In Sheet2!A1 =IF($Z$1="","-",$Z$1) in Sheet2!A2 =IF($A1="-","-", IF(COUNTIF(Sheet1!$A$1:$A$50,$A1)COUNTIF($A$1:$A1 ,$A1),$A1, IF(MATCH($A1,$Z$1:$Z$10)=COUNTA($Z$1:$Z$10),"-", INDEX($Z$1:$Z$10,MATCH($A1,$Z$1:$Z$10,0)+1)))) in Sheet2!B1:B2 =IF($A1="-","-", INDEX(Sheet1!B$1:B$50,LARGE(INDEX((Sheet1!$A$1:$A$ 50=$A1)* (ROW(Sheet1!$A$1:$A$50)-CELL("ROW",Sheet1!$A$1:$A$50)+1),), COUNTIF(Sheet1!$A$1:$A$50,$A1)+1-COUNTIF($A$1:$A1,$A1)))) Copy A2:B2 down as far as required, then copy B2:Bx to C2:Cx (or, for other purposes, as far across as required). I suspect these could be simplified further, especially if that second one was re-created as an array formula (which I prefer to avoid where possible), but my eyes are starting to itch, so I'm off to bed. Night! HTH Steve D. "Joe" wrote in message ... Steve, Normally AutoFilter would work. The example I gave was an oversimplification of what I am doing. The summary table I am trying to create will be imported by Microsoft Streets & Trips. The data I am working with is actually a list of locations with their respective longitude / latitude. Streets & Trips can't handle filters and pivot tables. Therefore there is a need to create a new separate table to use for the import process. To add a few questions to your original answer (which worked by the way, thanks)... 1. How do I show more columns in my new summary table? 2. How do I add a second value for Excel to lookup? Once again, thanks for your help. Joe "Steve Dunn" wrote: Hi Joe, have you tried using AutoFilter? If you particularly need the results in a seperate table, follow this. Assuming your lookup table is in Sheet1 and your results table will be in Sheet2: Put your lookup value in Sheet2!A1, then in Sheet2!B1: =IF(COUNTIF(Sheet1!$A$1:$A$50,$A$1)=ROW(1:1), INDEX(Sheet1!$B$1:$B$50,LARGE(INDEX((Sheet1!$A$1:$ A$50=$A$1)* (ROW(Sheet1!$B$1:$B$50)-CELL("ROW",Sheet1!$B$1:$B$50)+1),), COUNTIF(Sheet1!$A$1:$A$50,$A$1)+1-ROW(1:1))),"-") in Sheet2!A2: =IF(COUNTIF(Sheet1!$A$1:$A$50,$A$1)=ROW(2:2),$A$1 ,"-") Copy Sheet2!B1 to Sheet2!B2, then copy Sheet2!A2:B2 down as far as required. "Joe" wrote in message ... I want Excel to lookup a value in a table and return each row it finds that value in. As an example: Lookup Value: Ford Lookup Table: A1: Ford B1: Focus A2: Chevy B2: Malibu A3: Chevy B3: S10 A4: Ford B4: Ranger Results Table: A1: Ford B1: Focus A2: Ford B2: Ranger What I dont want is: A1: Ford B1: Focus A2: B2: A3: B3: A4: Ford B4: Ranger In other words, I dont want a bunch of blank rows in the new summary table. What function(s) can I use to create my summary table? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup / Summary Table
Slight amendments: the first MATCH in the second formula should have ,0 at
the end, and using SMALL rather than LARGE (with slight change to the final argument) may make the third formula a little more readable. =IF($A1="-","-", IF(COUNTIF(Sheet1!$A$1:$A$50,$A1)COUNTIF($A$1:$A1 ,$A1),$A1, IF(MATCH($A1,$Z$1:$Z$10,0)=COUNTA($Z$1:$Z$10),"-", INDEX($Z$1:$Z$10,MATCH($A1,$Z$1:$Z$10,0)+1)))) =IF($A1="-","-", INDEX(Sheet1!B$1:B$50,SMALL(INDEX((Sheet1!$A$1:$A$ 50=$A1)* (ROW(Sheet1!$A$1:$A$50)-CELL("ROW",Sheet1!$A$1:$A$50)+1),), COUNTIF(Sheet1!$A$1:$A$50,"<"&$A1)+COUNTIF($A$1:$ A1,$A1)))) "Steve Dunn" wrote in message ... Ok here goes... (Put your list of criteria in Sheet2!Z1:Z10 for this example - obviously you can change that reference to whatever you need.) In Sheet2!A1 =IF($Z$1="","-",$Z$1) in Sheet2!A2 =IF($A1="-","-", IF(COUNTIF(Sheet1!$A$1:$A$50,$A1)COUNTIF($A$1:$A1 ,$A1),$A1, IF(MATCH($A1,$Z$1:$Z$10)=COUNTA($Z$1:$Z$10),"-", INDEX($Z$1:$Z$10,MATCH($A1,$Z$1:$Z$10,0)+1)))) in Sheet2!B1:B2 =IF($A1="-","-", INDEX(Sheet1!B$1:B$50,LARGE(INDEX((Sheet1!$A$1:$A$ 50=$A1)* (ROW(Sheet1!$A$1:$A$50)-CELL("ROW",Sheet1!$A$1:$A$50)+1),), COUNTIF(Sheet1!$A$1:$A$50,$A1)+1-COUNTIF($A$1:$A1,$A1)))) Copy A2:B2 down as far as required, then copy B2:Bx to C2:Cx (or, for other purposes, as far across as required). I suspect these could be simplified further, especially if that second one was re-created as an array formula (which I prefer to avoid where possible), but my eyes are starting to itch, so I'm off to bed. Night! HTH Steve D. "Joe" wrote in message ... Steve, Normally AutoFilter would work. The example I gave was an oversimplification of what I am doing. The summary table I am trying to create will be imported by Microsoft Streets & Trips. The data I am working with is actually a list of locations with their respective longitude / latitude. Streets & Trips can't handle filters and pivot tables. Therefore there is a need to create a new separate table to use for the import process. To add a few questions to your original answer (which worked by the way, thanks)... 1. How do I show more columns in my new summary table? 2. How do I add a second value for Excel to lookup? Once again, thanks for your help. Joe "Steve Dunn" wrote: Hi Joe, have you tried using AutoFilter? If you particularly need the results in a seperate table, follow this. Assuming your lookup table is in Sheet1 and your results table will be in Sheet2: Put your lookup value in Sheet2!A1, then in Sheet2!B1: =IF(COUNTIF(Sheet1!$A$1:$A$50,$A$1)=ROW(1:1), INDEX(Sheet1!$B$1:$B$50,LARGE(INDEX((Sheet1!$A$1:$ A$50=$A$1)* (ROW(Sheet1!$B$1:$B$50)-CELL("ROW",Sheet1!$B$1:$B$50)+1),), COUNTIF(Sheet1!$A$1:$A$50,$A$1)+1-ROW(1:1))),"-") in Sheet2!A2: =IF(COUNTIF(Sheet1!$A$1:$A$50,$A$1)=ROW(2:2),$A$1 ,"-") Copy Sheet2!B1 to Sheet2!B2, then copy Sheet2!A2:B2 down as far as required. "Joe" wrote in message ... I want Excel to lookup a value in a table and return each row it finds that value in. As an example: Lookup Value: Ford Lookup Table: A1: Ford B1: Focus A2: Chevy B2: Malibu A3: Chevy B3: S10 A4: Ford B4: Ranger Results Table: A1: Ford B1: Focus A2: Ford B2: Ranger What I dont want is: A1: Ford B1: Focus A2: B2: A3: B3: A4: Ford B4: Ranger In other words, I dont want a bunch of blank rows in the new summary table. What function(s) can I use to create my summary table? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup / Summary Table
Worked like a charm. Thanks for all your help!
"Steve Dunn" wrote: Slight amendments: the first MATCH in the second formula should have ,0 at the end, and using SMALL rather than LARGE (with slight change to the final argument) may make the third formula a little more readable. =IF($A1="-","-", IF(COUNTIF(Sheet1!$A$1:$A$50,$A1)COUNTIF($A$1:$A1 ,$A1),$A1, IF(MATCH($A1,$Z$1:$Z$10,0)=COUNTA($Z$1:$Z$10),"-", INDEX($Z$1:$Z$10,MATCH($A1,$Z$1:$Z$10,0)+1)))) =IF($A1="-","-", INDEX(Sheet1!B$1:B$50,SMALL(INDEX((Sheet1!$A$1:$A$ 50=$A1)* (ROW(Sheet1!$A$1:$A$50)-CELL("ROW",Sheet1!$A$1:$A$50)+1),), COUNTIF(Sheet1!$A$1:$A$50,"<"&$A1)+COUNTIF($A$1:$ A1,$A1)))) "Steve Dunn" wrote in message ... Ok here goes... (Put your list of criteria in Sheet2!Z1:Z10 for this example - obviously you can change that reference to whatever you need.) In Sheet2!A1 =IF($Z$1="","-",$Z$1) in Sheet2!A2 =IF($A1="-","-", IF(COUNTIF(Sheet1!$A$1:$A$50,$A1)COUNTIF($A$1:$A1 ,$A1),$A1, IF(MATCH($A1,$Z$1:$Z$10)=COUNTA($Z$1:$Z$10),"-", INDEX($Z$1:$Z$10,MATCH($A1,$Z$1:$Z$10,0)+1)))) in Sheet2!B1:B2 =IF($A1="-","-", INDEX(Sheet1!B$1:B$50,LARGE(INDEX((Sheet1!$A$1:$A$ 50=$A1)* (ROW(Sheet1!$A$1:$A$50)-CELL("ROW",Sheet1!$A$1:$A$50)+1),), COUNTIF(Sheet1!$A$1:$A$50,$A1)+1-COUNTIF($A$1:$A1,$A1)))) Copy A2:B2 down as far as required, then copy B2:Bx to C2:Cx (or, for other purposes, as far across as required). I suspect these could be simplified further, especially if that second one was re-created as an array formula (which I prefer to avoid where possible), but my eyes are starting to itch, so I'm off to bed. Night! HTH Steve D. "Joe" wrote in message ... Steve, Normally AutoFilter would work. The example I gave was an oversimplification of what I am doing. The summary table I am trying to create will be imported by Microsoft Streets & Trips. The data I am working with is actually a list of locations with their respective longitude / latitude. Streets & Trips can't handle filters and pivot tables. Therefore there is a need to create a new separate table to use for the import process. To add a few questions to your original answer (which worked by the way, thanks)... 1. How do I show more columns in my new summary table? 2. How do I add a second value for Excel to lookup? Once again, thanks for your help. Joe "Steve Dunn" wrote: Hi Joe, have you tried using AutoFilter? If you particularly need the results in a seperate table, follow this. Assuming your lookup table is in Sheet1 and your results table will be in Sheet2: Put your lookup value in Sheet2!A1, then in Sheet2!B1: =IF(COUNTIF(Sheet1!$A$1:$A$50,$A$1)=ROW(1:1), INDEX(Sheet1!$B$1:$B$50,LARGE(INDEX((Sheet1!$A$1:$ A$50=$A$1)* (ROW(Sheet1!$B$1:$B$50)-CELL("ROW",Sheet1!$B$1:$B$50)+1),), COUNTIF(Sheet1!$A$1:$A$50,$A$1)+1-ROW(1:1))),"-") in Sheet2!A2: =IF(COUNTIF(Sheet1!$A$1:$A$50,$A$1)=ROW(2:2),$A$1 ,"-") Copy Sheet2!B1 to Sheet2!B2, then copy Sheet2!A2:B2 down as far as required. "Joe" wrote in message ... I want Excel to lookup a value in a table and return each row it finds that value in. As an example: Lookup Value: Ford Lookup Table: A1: Ford B1: Focus A2: Chevy B2: Malibu A3: Chevy B3: S10 A4: Ford B4: Ranger Results Table: A1: Ford B1: Focus A2: Ford B2: Ranger What I dont want is: A1: Ford B1: Focus A2: B2: A3: B3: A4: Ford B4: Ranger In other words, I dont want a bunch of blank rows in the new summary table. What function(s) can I use to create my summary table? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup / Summary Table
You're welcome Joe, glad to help.
"Joe" wrote in message ... Worked like a charm. Thanks for all your help! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lookup / Summary Table | Excel Worksheet Functions | |||
| Creating summary table from detail table | Excel Worksheet Functions | |||
| Excel Datalist functions / lookup summary | Excel Discussion (Misc queries) | |||
| lookup? list data into summary table | Excel Worksheet Functions | |||
| PIVOT TABLE - Summary Table into a Databasae Table. | Excel Worksheet Functions | |||
Linear Mode
