Lookup:2 criteria.

[cbanks]

How do i lookup with 2 criteria..

I have the date in column A, column b i have the region (ex Region 1), and i
want to return whats in column C.

=LOOKUP(TODAY(),Auto!A:A,Auto!C:C)

i need it to be more like

=LOOKUP(TODAY() & b:b Region1,Auto!A:A,Auto!C:C)

[Dave Peterson]

You can use this kind of syntax:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0))
(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

So for your example:

=index(auto!$c$1:$c$100,
match(1,(a2=auto!$a$1:$a$100)*(b2=auto!$b$1:$b$100),0))

Where A2 contains the data and B2 contains the region.



cbanks wrote:
>
> How do i lookup with 2 criteria..
>
> I have the date in column A, column b i have the region (ex Region 1), and i
> want to return whats in column C.
>
> =LOOKUP(TODAY(),Auto!A:A,Auto!C:C)
>
> i need it to be more like
>
> =LOOKUP(TODAY() & b:b Region1,Auto!A:A,Auto!C:C)

--

Dave Peterson

[cbanks]

im sorry this just confused me.. i tried typing it in but it didnt work.. see
post labled Lookup with Multiple Criteria.. I reposted with a little more
info.

"Dave Peterson" wrote:

> You can use this kind of syntax:
>
> =index(othersheet!$c$1:$c$100,
> match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0))
> (one cell)
>
> This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
> correctly, excel will wrap curly brackets {} around your formula. (don't type
> them yourself.)
>
> Adjust the range to match--but you can't use the whole column.
>
> So for your example:
>
> =index(auto!$c$1:$c$100,
> match(1,(a2=auto!$a$1:$a$100)*(b2=auto!$b$1:$b$100),0))
>
> Where A2 contains the data and B2 contains the region.
>
>
>
> cbanks wrote:
> >
> > How do i lookup with 2 criteria..
> >
> > I have the date in column A, column b i have the region (ex Region 1), and i
> > want to return whats in column C.
> >
> > =LOOKUP(TODAY(),Auto!A:A,Auto!C:C)
> >
> > i need it to be more like
> >
> > =LOOKUP(TODAY() & b:b Region1,Auto!A:A,Auto!C:C)
>
> --
>
> Dave Peterson
>