2 criteria lookup of text. Return text form column 3. SUMPRODUCT t
 

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

2 criteria lookup of text. Return text form column 3. SUMPRODUCT t
 

=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" wrote in message
...
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

2 criteria lookup of text. Return text form column 3. SUMPRODU
 

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" wrote in message
...
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