Lookup Multiple Criteria return One answer

Here is what I need to happen... Below is my spreadsheet.. i am trying to
keep a report that pulls information from this spreadsheet and updates each
day with that current days numbers... So on a seperate sheet I want it to
list the current days numbers for that cell.. If the date is 01/02/06 then i
want the region 1 total errors for that day.. I was thinking something like
this.
=LOOKUP(Auto!today(),Auto!A:A,Auto!C:C) but that only pulls 1 criteria and
is not working.. i need 2 criteria, date and region. Thanks

A B C
Date Region Total Errors
1/2/2006 Region 1 0
Region 2 683
Region 3 0
Region 4 0
Region 5 376
Region 6 569
Region 8 0
Region 9 145

First, I restructure the data table to facilitate lookups, but if that's the
structure you're stuck with then this may work for you:

Assumption: You want to view error information for a single date on a
separate worksheet.

With your example data on Sheet1, beginning in Cell A1

Using Sheet2:
B1: Date
B2: 01/02/2006
C1: Region
D1: Total Errors

A2: =MATCH(B2,Sheet1!A1:A1000,0)

C2: Region 1
C3: Region 2
etc

D2: =VLOOKUP(C2,INDEX(Sheet1!$B$1:$B$1000,Sheet2!$A$2,1):Sheet1!$C$1000,2,0)

Copy that formula down as far as you have regions listed.

Change the date in Cell B2 to another date and the formulas in Col_D will
return the relevent data for that date.

(Adjust range references to suit your situation)

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


"cbanks" wrote:

> Here is what I need to happen... Below is my spreadsheet.. i am trying to
> keep a report that pulls information from this spreadsheet and updates each
> day with that current days numbers... So on a seperate sheet I want it to
> list the current days numbers for that cell.. If the date is 01/02/06 then i
> want the region 1 total errors for that day.. I was thinking something like
> this.
> =LOOKUP(Auto!today(),Auto!A:A,Auto!C:C) but that only pulls 1 criteria and
> is not working.. i need 2 criteria, date and region. Thanks
>
> A B C
> Date Region Total Errors
> 1/2/2006 Region 1 0
> Region 2 683
> Region 3 0
> Region 4 0
> Region 5 376
> Region 6 569
> Region 8 0
> Region 9 145
>
>

You've got a couple of other threads going elsewhere.

cbanks wrote:
>
> Here is what I need to happen... Below is my spreadsheet.. i am trying to
> keep a report that pulls information from this spreadsheet and updates each
> day with that current days numbers... So on a seperate sheet I want it to
> list the current days numbers for that cell.. If the date is 01/02/06 then i
> want the region 1 total errors for that day.. I was thinking something like
> this.
> =LOOKUP(Auto!today(),Auto!A:A,Auto!C:C) but that only pulls 1 criteria and
> is not working.. i need 2 criteria, date and region. Thanks
>
> A B C
> Date Region Total Errors
> 1/2/2006 Region 1 0
> Region 2 683
> Region 3 0
> Region 4 0
> Region 5 376
> Region 6 569
> Region 8 0
> Region 9 145

--

Dave Peterson

no not really my data needs to stay the same.. im putting together a report
and i want it to just show everyday whats happening that day.. so i need my
columns to stay the same.

"Ron Coderre" wrote:

> First, I restructure the data table to facilitate lookups, but if that's the
> structure you're stuck with then this may work for you:
>
> Assumption: You want to view error information for a single date on a
> separate worksheet.
>
> With your example data on Sheet1, beginning in Cell A1
>
> Using Sheet2:
> B1: Date
> B2: 01/02/2006
> C1: Region
> D1: Total Errors
>
> A2: =MATCH(B2,Sheet1!A1:A1000,0)
>
> C2: Region 1
> C3: Region 2
> etc
>
> D2: =VLOOKUP(C2,INDEX(Sheet1!$B$1:$B$1000,Sheet2!$A$2,1):Sheet1!$C$1000,2,0)
>
> Copy that formula down as far as you have regions listed.
>
> Change the date in Cell B2 to another date and the formulas in Col_D will
> return the relevent data for that date.
>
> (Adjust range references to suit your situation)
>
> Is that something you can work with?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "cbanks" wrote:
>
> > Here is what I need to happen... Below is my spreadsheet.. i am trying to
> > keep a report that pulls information from this spreadsheet and updates each
> > day with that current days numbers... So on a seperate sheet I want it to
> > list the current days numbers for that cell.. If the date is 01/02/06 then i
> > want the region 1 total errors for that day.. I was thinking something like
> > this.
> > =LOOKUP(Auto!today(),Auto!A:A,Auto!C:C) but that only pulls 1 criteria and
> > is not working.. i need 2 criteria, date and region. Thanks
> >
> > A B C
> > Date Region Total Errors
> > 1/2/2006 Region 1 0
> > Region 2 683
> > Region 3 0
> > Region 4 0
> > Region 5 376
> > Region 6 569
> > Region 8 0
> > Region 9 145
> >
> >