Hope someone can help here!

Code Year Jan Feb Mar Apr... ect

3/02 2004 190 189 191 191
3/02 2005 205 206 208 209
3/03 2004 178 178 181 182
3/03 2005 185 186 185 187

I need to look up a value by selecting a code ref ie 3/02, a year, and a month
My source data sheet has 1600 rows and is set up like this & I don't want to
change it! I have 10 years of data with over 60 different codes
I intend to ask a user to select code; year; month from drop down boxes and
present a single value eg code 3/02; year 2005; month Mar = 208

Can't help thinking there must be an easier way, but assuming your data is
in A1:N1600, and your lookup values are

Code in cell Z4
Year in cell Z5
Month in Cell Z6

=INDEX($A$1:$N$1600,SUMPRODUCT(--($A$1:$A$1600=Z4),--($B$1:$B$1600=Z5),ROW($A$1:$A$1600)),MATCH(Z6,$A$1 :$N$1,0))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"Saintsman" wrote in message
...
Hope someone can help here!

Code Year Jan Feb Mar Apr... ect

3/02 2004 190 189 191 191
3/02 2005 205 206 208 209
3/03 2004 178 178 181 182
3/03 2005 185 186 185 187

I need to look up a value by selecting a code ref ie 3/02, a year, and a
month
My source data sheet has 1600 rows and is set up like this & I don't want
to
change it! I have 10 years of data with over 60 different codes
I intend to ask a user to select code; year; month from drop down boxes
and
present a single value eg code 3/02; year 2005; month Mar = 208

Thanks Ken - this worked fine!

"Ken Wright" wrote:

Can't help thinking there must be an easier way, but assuming your data is
in A1:N1600, and your lookup values are

Code in cell Z4
Year in cell Z5
Month in Cell Z6

=INDEX($A$1:$N$1600,SUMPRODUCT(--($A$1:$A$1600=Z4),--($B$1:$B$1600=Z5),ROW($A$1:$A$1600)),MATCH(Z6,$A$1 :$N$1,0))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------Â*------------------------------Â*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------Â*------------------------------Â*----------------


"Saintsman" wrote in message
...
Hope someone can help here!

Code Year Jan Feb Mar Apr... ect

3/02 2004 190 189 191 191
3/02 2005 205 206 208 209
3/03 2004 178 178 181 182
3/03 2005 185 186 185 187

I need to look up a value by selecting a code ref ie 3/02, a year, and a
month
My source data sheet has 1600 rows and is set up like this & I don't want
to
change it! I have 10 years of data with over 60 different codes
I intend to ask a user to select code; year; month from drop down boxes
and
present a single value eg code 3/02; year 2005; month Mar = 208

Source data : A1:N1600
Code in cell O1
Year in cell O2
Month in Cell O3

Note : Row 1 is the table header (Code,Year,Jan,Feb,...)

=SUM(IF(A2:A1600=O1,IF(B2:B1600=O2,OFFSET(A1,1,MAT CH(O3,C1:N1)+1,1600-1,1))))

Juz paste this formula into the formula bar but MAKE SURE YOU PASTE THEM AS
ARRAY. Don't press ENTER but Ctrl+Shift+ENTER...

So in the formula bar it would be like :
{=SUM(IF(...))))}

"Ken Wright" wrote:

Can't help thinking there must be an easier way, but assuming your data is
in A1:N1600, and your lookup values are

Code in cell Z4
Year in cell Z5
Month in Cell Z6

=INDEX($A$1:$N$1600,SUMPRODUCT(--($A$1:$A$1600=Z4),--($B$1:$B$1600=Z5),ROW($A$1:$A$1600)),MATCH(Z6,$A$1 :$N$1,0))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------Â*------------------------------Â*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------Â*------------------------------Â*----------------


"Saintsman" wrote in message
...
Hope someone can help here!

Code Year Jan Feb Mar Apr... ect

3/02 2004 190 189 191 191
3/02 2005 205 206 208 209
3/03 2004 178 178 181 182
3/03 2005 185 186 185 187

I need to look up a value by selecting a code ref ie 3/02, a year, and a
month
My source data sheet has 1600 rows and is set up like this & I don't want
to
change it! I have 10 years of data with over 60 different codes
I intend to ask a user to select code; year; month from drop down boxes
and
present a single value eg code 3/02; year 2005; month Mar = 208