Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Choosing data based on Match to several items
I have a range of data that is organized based on 5 data points. I would
like to develop a formula the chooses the proper data based on the values in the five data points. Is there an easy way to do that? I have looked at the Choose, Match, and Index functions and none seem to do what I'm wanting them to do. Below is an example of my spreadsheet: Data Table Data Points Data 2006 2007 2008 2009 2010 ... 1 1 3 0 1 5 6 5 4 3 1 2 1 0 1 7 8 6 4 2 2 1 2 0 0 10 12 9 7 5 Selected Data (what I want to see) 1 2 1 0 1 7 8 6 4 2 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Choosing data based on Match to several items
From your post, it just looks like you want the 2nd row of data from the table.
Can you post what methodology you want to use to select that data? *********** Regards, Ron XL2002, WinXP "Nipper" wrote: I have a range of data that is organized based on 5 data points. I would like to develop a formula the chooses the proper data based on the values in the five data points. Is there an easy way to do that? I have looked at the Choose, Match, and Index functions and none seem to do what I'm wanting them to do. Below is an example of my spreadsheet: Data Table Data Points Data 2006 2007 2008 2009 2010 ... 1 1 3 0 1 5 6 5 4 3 1 2 1 0 1 7 8 6 4 2 2 1 2 0 0 10 12 9 7 5 Selected Data (what I want to see) 1 2 1 0 1 7 8 6 4 2 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Choosing data based on Match to several items
If you can use an additional column, one solution can be creating a
concatenation of the codes, and the use VLOOKUP over it. In your example, to create the column you may use: =CONCATENATE(A2,B2,C2,D2,E2) And to query it (supposing the 5 entry points are in A7:E7 and the new column is in F, with the data on their right), something like: =VLOOKUP(CONCATENATE(A7,B7,C7,D7,E7,),$F$2:$K$4,2, FALSE) Hope this helps, Miguel. "Nipper" wrote: I have a range of data that is organized based on 5 data points. I would like to develop a formula the chooses the proper data based on the values in the five data points. Is there an easy way to do that? I have looked at the Choose, Match, and Index functions and none seem to do what I'm wanting them to do. Below is an example of my spreadsheet: Data Table Data Points Data 2006 2007 2008 2009 2010 ... 1 1 3 0 1 5 6 5 4 3 1 2 1 0 1 7 8 6 4 2 2 1 2 0 0 10 12 9 7 5 Selected Data (what I want to see) 1 2 1 0 1 7 8 6 4 2 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Choosing data based on Match to several items
Assuming there are no dupes in data point combination, you can use formula:
=SUMPRODUCT(($A10=$A$2:$A$4)*($B10=$B$2:$B$4)*($C1 0=$C$2:$C$4)*($D10=$D$2:$D$4)*($E10=$E$2:$E$4),F2: F4) HTH -- AP "Nipper" a écrit dans le message de news: ... I have a range of data that is organized based on 5 data points. I would like to develop a formula the chooses the proper data based on the values in the five data points. Is there an easy way to do that? I have looked at the Choose, Match, and Index functions and none seem to do what I'm wanting them to do. Below is an example of my spreadsheet: Data Table Data Points Data 2006 2007 2008 2009 2010 ... 1 1 3 0 1 5 6 5 4 3 1 2 1 0 1 7 8 6 4 2 2 1 2 0 0 10 12 9 7 5 Selected Data (what I want to see) 1 2 1 0 1 7 8 6 4 2 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Choosing data based on Match to several items
Thanks for your suggestion. I actually came up with about the same answer
except I decided to convert the five data points into a unique number by multiplying the first data point by 10,000, the second by 1,000, the third by 100 and so on. Thank you very much, everyone, for your responses. Nipper "Miguel Zapico" wrote: If you can use an additional column, one solution can be creating a concatenation of the codes, and the use VLOOKUP over it. In your example, to create the column you may use: =CONCATENATE(A2,B2,C2,D2,E2) And to query it (supposing the 5 entry points are in A7:E7 and the new column is in F, with the data on their right), something like: =VLOOKUP(CONCATENATE(A7,B7,C7,D7,E7,),$F$2:$K$4,2, FALSE) Hope this helps, Miguel. "Nipper" wrote: I have a range of data that is organized based on 5 data points. I would like to develop a formula the chooses the proper data based on the values in the five data points. Is there an easy way to do that? I have looked at the Choose, Match, and Index functions and none seem to do what I'm wanting them to do. Below is an example of my spreadsheet: Data Table Data Points Data 2006 2007 2008 2009 2010 ... 1 1 3 0 1 5 6 5 4 3 1 2 1 0 1 7 8 6 4 2 2 1 2 0 0 10 12 9 7 5 Selected Data (what I want to see) 1 2 1 0 1 7 8 6 4 2 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Choosing data based on Match to several items
I think I understand...try something like this:
In this example I rearranged the data to (hopefully) make it more understandable: With: Your criteria data in A1:J2, years in A1:J1 and values in A2:J2 Your data table in A5:J8, years in A5:J5 and values below them Add these to Col_K K5: RowRef K6: =ROW() Copy that formula down through K8 M1: RowRef Select the new data range: A5:K8 <data<filter<autofilter Check: Copy to another location List Range: (already selected $A$5:$K$8) Criteria range: $A$1:$J$2 Copy to: $M$1 Click the [OK] button All RowRefs for rows that exactly match the criteria will be listed under cell M1. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: From your post, it just looks like you want the 2nd row of data from the table. Can you post what methodology you want to use to select that data? *********** Regards, Ron XL2002, WinXP "Nipper" wrote: I have a range of data that is organized based on 5 data points. I would like to develop a formula the chooses the proper data based on the values in the five data points. Is there an easy way to do that? I have looked at the Choose, Match, and Index functions and none seem to do what I'm wanting them to do. Below is an example of my spreadsheet: Data Table Data Points Data 2006 2007 2008 2009 2010 ... 1 1 3 0 1 5 6 5 4 3 1 2 1 0 1 7 8 6 4 2 2 1 2 0 0 10 12 9 7 5 Selected Data (what I want to see) 1 2 1 0 1 7 8 6 4 2 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
return multiple rows of data based on criteria | Excel Worksheet Functions | |||
How to Match and Sort two range of data? | New Users to Excel | |||
Sort pages? | Excel Discussion (Misc queries) | |||
How do I get a pivot table to sort based on the data items sectio. | Excel Worksheet Functions | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |