Choosing data based on Match to several items

[Guest]

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

 

[Guest]

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

 

[Guest]

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.

 

[Ardus Petus]

Assuming there are no dupes in data point combination, you can use formula:
=SUMPRODUCT(($A10=$A$2:$A$4)*($B10=$B$2:$B$4)*($C10=$C$2:$C$4)*($D10=$D$2:$D$4)*($E10=$E$2:$E$4),F2:F4)

HTH

 

[Guest]

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

 

[Guest]

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