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

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

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

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

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

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