VLOOKUP needing to match two cells

[Liz Steffen]

I have a worksheet with data. I need to match the date and the shift and
return the value to a summary page.

Sample data:
Date Shift AvailMin Oper_BreaksOper_StickerMach
2/9/2009 2 480 60 0
5/5/2006 2 480 0 0
5/5/2006 1 480 24 14

So, I need to lookup Date and Shift and return to the cell the available
minutes.

Sheet sample of where data goes to:

5/9/2006
DAILY

Operational
Shift 1 Shift 2
Breaks (value of 24 would go here)
Sticker Machine
Breakdown Infeed
Breakdown Hoist
Infeed Lug Loader
Infeed Moisture Meter
Infeed Tipple
Infeed Table

--
-----
Thank you,
Liz

[Bob Umlas, Excel MVP]

If that 24 is in column D and the sheet name is "Sheet1", for example, and if
the date 5/5 is in cell C2 of the Summary sheet, (lots of assumptions!) then
this formula in the Summary sheet would work:
=INDEX(Sheet1!D:D,MATCH(C2&"1",Sheet1!A1:A100&Sheet1!B1:B100,0))
This formula needs to be ctrl/shift/entered, not simply entered.
the C2&"1" is because C2 contains 5/5/2006 and the "1" is the shift.
HTH

"Liz Steffen" wrote:

> I have a worksheet with data. I need to match the date and the shift and
> return the value to a summary page.
>
> Sample data:
> Date Shift AvailMin Oper_BreaksOper_StickerMach
> 2/9/2009 2 480 60 0
> 5/5/2006 2 480 0 0
> 5/5/2006 1 480 24 14
>
> So, I need to lookup Date and Shift and return to the cell the available
> minutes.
>
> Sheet sample of where data goes to:
>
> 5/9/2006
> DAILY
>
> Operational
> Shift 1 Shift 2
> Breaks (value of 24 would go here)
> Sticker Machine
> Breakdown Infeed
> Breakdown Hoist
> Infeed Lug Loader
> Infeed Moisture Meter
> Infeed Tipple
> Infeed Table
>
> --
> -----
> Thank you,
> Liz
>

[Peo Sjoblom]

=INDEX(Minute_Range,MATCH(1,(Date_Range=lookup1)*(Shift_Range=lookup2),0))

entered with ctrl + shift & enter


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"Liz Steffen" <[email protected]> wrote in message
news:[email protected]...
> I have a worksheet with data. I need to match the date and the shift and
> return the value to a summary page.
>
> Sample data:
> Date Shift AvailMin Oper_BreaksOper_StickerMach
> 2/9/2009 2 480 60 0
> 5/5/2006 2 480 0 0
> 5/5/2006 1 480 24 14
>
> So, I need to lookup Date and Shift and return to the cell the available
> minutes.
>
> Sheet sample of where data goes to:
>
> 5/9/2006
> DAILY
>
> Operational
> Shift 1 Shift 2
> Breaks (value of 24 would go here)
> Sticker Machine
> Breakdown Infeed
> Breakdown Hoist
> Infeed Lug Loader
> Infeed Moisture Meter
> Infeed Tipple
> Infeed Table
>
> --
> -----
> Thank you,
> Liz
>

[Liz Steffen]

Hi Bob,

You have been very helpful. Here is my syntax and it's not working.

=INDEX(Header!D:D,MATCH(A2&"1",Header!A2:A700&Header!B2:B700,0))

Can you help some more?

--
-----
Thank you,
Liz



"Bob Umlas, Excel MVP" wrote:

> If that 24 is in column D and the sheet name is "Sheet1", for example, and if
> the date 5/5 is in cell C2 of the Summary sheet, (lots of assumptions!) then
> this formula in the Summary sheet would work:
> =INDEX(Sheet1!D:D,MATCH(C2&"1",Sheet1!A1:A100&Sheet1!B1:B100,0))
> This formula needs to be ctrl/shift/entered, not simply entered.
> the C2&"1" is because C2 contains 5/5/2006 and the "1" is the shift.
> HTH
>
> "Liz Steffen" wrote:
>
> > I have a worksheet with data. I need to match the date and the shift and
> > return the value to a summary page.
> >
> > Sample data:
> > Date Shift AvailMin Oper_BreaksOper_StickerMach
> > 2/9/2009 2 480 60 0
> > 5/5/2006 2 480 0 0
> > 5/5/2006 1 480 24 14
> >
> > So, I need to lookup Date and Shift and return to the cell the available
> > minutes.
> >
> > Sheet sample of where data goes to:
> >
> > 5/9/2006
> > DAILY
> >
> > Operational
> > Shift 1 Shift 2
> > Breaks (value of 24 would go here)
> > Sticker Machine
> > Breakdown Infeed
> > Breakdown Hoist
> > Infeed Lug Loader
> > Infeed Moisture Meter
> > Infeed Tipple
> > Infeed Table
> >
> > --
> > -----
> > Thank you,
> > Liz
> >