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

Z

zzxxcc

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.
 
B

Bob Phillips

=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.
 
Z

zzxxcc

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 said:
=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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top