how to search a sheet using row and column headers

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
Fri May 09 09:35:48 CDT 2008

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

On May 9, 2:40=A0pm, shell96 <shel...@discussions.microsoft.com> wrote:
> 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. =A0the table i want to populate has a range of age groups d=
own
> 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. =A0=
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, t=
he
> 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 th=
e
> date then all the data in the table will change accordingly. =A0I dont min=
d
> 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. =A0I cant believ=
e
> there is no way of searching a table using more than one variable but i ca=
nt
> figure it out! can anybody help with this?

shell96
Fri May 09 09:56:09 CDT 2008

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 :)

"Pete_UK" wrote:

> 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
>
> On May 9, 2:40 pm, shell96 <shel...@discussions.microsoft.com> wrote:
> > 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?
>
>

demechanik
Fri May 09 11:08:33 CDT 2008

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---