how to search a sheet using row and column headers

[shell96]

There may be an easy solution to this query as it seems quite simple but i'm
tying my brain in knots trying to figure it out!

I have a table in a sheet which i want to populate with data from a table in
another sheet. the table i want to populate has a range of age groups down
the column and some different types of households (eg, 1 person male, 1
person female, 2 person 2 adult, 2 person 1 parent,etc) along the row. There
is a cell above it for the year.

The table i want to use to fill this one has the age ranges in column B, the
household types in column C and the years from 2006 to 2031 along row 2.

Ideally i would like to be able to write a formaula in the cells of the
table i need to fill so that if i change the cell at the top of it with the
date then all the data in the table will change accordingly. I dont mind
putting in the complicated work just now if it will save me time in future
years.

Vlookup and Hlookup only let me search one row or column. I cant believe
there is no way of searching a table using more than one variable but i cant
figure it out! can anybody help with this?

 

[Pete_UK]

I'm not sure exactly how your source table is laid out - where does
2006 start, and what is contained in the year columns? Is it, as I
suspect, a summary table, and you just want to pick out one particular
year's values and transpose them into another table for that year?

Pete

 

[shell96]

Hi Pete,

below is an example of my source table as i cant get it to copy and paste
very well. Basically i wanted a function that would check the year, age
group and household type and return the number. i've tried some suggestions
left on posts by others and have got it almost how i want but not quite. It
seems happy enough to search more than one column but doesnt seem to like to
look through both columns and rows so i'm starting to suspect maybe it just
cant be done in which case i'll have to settle for what i've done so far. i
can get it to search based on the column for one year only (rather than
searching through the years) which i guess is easy enough to amend each year
without much work.

B C D E F G H

2006 2007 2008 2009 2010
age group household type
16-24 1 person female 0.2 0.4 0.1 0.1 0.6
16-24 1 person male 0.5 0.6 0.4 0.4 0.8
16-24 2 person 1 parent 0.2 0.4 0.1 0.1 0.6
25-29 1 person female 0.2 0.4 0.1 0.1 0.6
25-29 1 person male 0.3 0.4 0.8 0.5 0.6
25-29 2 person 1 parent 0.5 0.6 0.4 0.4 0.8

Thanks for your quick response

 

[Max]

Here's one possible approach,
illustrated in this sample:
http://www.freefilehosting.net/download/3h27g
Search Table.xls

With selections/inputs for age group, hsehold type, year in A14:C14,
Array-enter* in D14, copy down:
=IF(COUNTA(A14:C14)<3,"",INDEX($C$3:$G$8,MATCH(1,($A$3:$A$8=A14)*($B$3:$B$8=B14),0),MATCH(C14,$C$2:$G$2,0)))
*Press CTRL+SHIFT+ENTER to confirm the formula