Hi
Can I use SUMPRODUCT to lookup two criteria text values in sheet1, cell
A2:A100 and B2:B100 and return the text from C2:C100? The formula will be in
sheet2, A4:C4. One criteria in sheet2, $A$1 and the rest in sheet2, A4:A100.
Filtering and pivot tables won't do.

Example sheet1:
A: B: C:
Job status Work-area Job status
Running Office Some work left
Stopped Workshop All done
Running Warehouse Missing one item
....

Result in sheet 2:
A: B: C:
Running

Office: Warehouse: Workshop:
Some work left Missing one item

Maybe I need to use MATCH and INDEX? I also tried to combine two columns
into one and then use VLOOKUP. Didn't work. Any help will be appreciated.
--
Thanks
John

=INDEX(Sheet1!C:C,MATCH(1,(Sheet1!$A$2:$A$200=A1)*(Sheet1!$B$2:$B$200=B1),0))

this is an array formula, so ciomiit with Ctrl-Shift-Enter, not just Enter.

--
__________________________________
HTH

Bob

"zzxxcc" <(E-Mail Removed)> wrote in message
news:7E9F6A6A-CCE2-4EBF-9D2F-(E-Mail Removed)...
> Hi
> Can I use SUMPRODUCT to lookup two criteria text values in sheet1, cell
> A2:A100 and B2:B100 and return the text from C2:C100? The formula will be
> in
> sheet2, A4:C4. One criteria in sheet2, $A$1 and the rest in sheet2,
> A4:A100.
> Filtering and pivot tables won't do.
>
> Example sheet1:
> A: B: C:
> Job status Work-area Job status
> Running Office Some work left
> Stopped Workshop All done
> Running Warehouse Missing one item
> ...
>
> Result in sheet 2:
> A: B: C:
> Running
>
> Office: Warehouse: Workshop:
> Some work left Missing one item
>
> Maybe I need to use MATCH and INDEX? I also tried to combine two columns
> into one and then use VLOOKUP. Didn't work. Any help will be appreciated.
> --
> Thanks
> John

Bob,
My mistake: The criteria No.1 is in sheet2, $A$1 and the rest in sheet2,
A3:BJ3. (A3:C3 for this example, but the table actually extend beyond col.C).

I adjusted accordingly, entered with ctrl+shift+enter , but still it didn't
work.
--
Thanks
John

"Bob Phillips" wrote:

> =INDEX(Sheet1!C:C,MATCH(1,(Sheet1!$A$2:$A$200=A1)*(Sheet1!$B$2:$B$200=B1),0))
>
> this is an array formula, so ciomiit with Ctrl-Shift-Enter, not just Enter.
>
> --
> __________________________________
> HTH
>
> Bob
>
> "zzxxcc" <(E-Mail Removed)> wrote in message
> news:7E9F6A6A-CCE2-4EBF-9D2F-(E-Mail Removed)...
> > Hi
> > Can I use SUMPRODUCT to lookup two criteria text values in sheet1, cell
> > A2:A100 and B2:B100 and return the text from C2:C100? The formula will be
> > in
> > sheet2, A4:C4. One criteria in sheet2, $A$1 and the rest in sheet2,
> > A4:A100.
> > Filtering and pivot tables won't do.
> >
> > Example sheet1:
> > A: B: C:
> > Job status Work-area Job status
> > Running Office Some work left
> > Stopped Workshop All done
> > Running Warehouse Missing one item
> > ...
> >
> > Result in sheet 2:
> > A: B: C:
> > Running
> >
> > Office: Warehouse: Workshop:
> > Some work left Missing one item
> >
> > Maybe I need to use MATCH and INDEX? I also tried to combine two columns
> > into one and then use VLOOKUP. Didn't work. Any help will be appreciated.
> > --
> > Thanks
> > John
>
>
>