If A1 and B1 match a row within A3:B10, then show values of matched row in columns C, D & E.

A B C D E

CLASS CAT ST Rate OT Rate Sun/Hol Rate

1 Laborer Group 5

2 CLASS CAT ST Rate OT Rate Sun/Hol Rate

3 Laborer Group 1 $9.00 $13.50 $18.00

4 Laborer Group 2 $9.50 $14.25 $19.00

5 Laborer Group 3 $10.00 $15.00 $20.00

6 Laborer Group 4 $10.50 $15.75 $21.00

7 Laborer Group 5 $11.00 $16.50 $22.00

8 Laborer Group 6 $11.50 $17.25 $23.00

9 Laborer Group 7 $12.00 $18.00 $24.00

10 Laborer Group 8 $12.50 $18.75 $25.00FOR C1, answer should be “$11.00”: =IF(AND(A1=A3:A10, B1=B3:B10),C3:C10,””)

FOR D1, answer should be “$16.50”: =IF(AND(A1=A3:A10, B1=B3:B10),D3:D10,””)

FOR E1, answer should be “$22.00”: =IF(AND(A1=A3:A10, B1=B3:B10),E3:E10,””)OR

FOR C1, answer should be “$11.00”: =IF(AND(COUNTIF(A3:A10,A1),COUNTIF(B3:B10,B1)),C3:C10,"")

FOR D1, answer should be “$16.50”: =IF(AND(COUNTIF(A3:A10,A1),COUNTIF(B3:B10,B1)),D3:D10,"")

FOR E1, answer should be “$22.00”: =IF(AND(COUNTIF(A3:A10,A1),COUNTIF(B3:B10,B1)),E3:E10,"")What’s wrong with my statements. Please advise. Thanks.

Try this: With your data looking like this:

A B C D E 1) Laborer Group 5 $11.00 $16.50 $22.00 2) 3) Laborer Group 1 $9.00 $13.50 $18.00 4) Laborer Group 2 $9.50 $14.25 $19.00 5) Laborer Group 3 $10.00 $15.00 $20.00 6) Laborer Group 4 $10.50 $15.75 $21.00 7) Laborer Group 5 $11.00 $16.50 $22.00 8) Laborer Group 6 $11.50 $17.25 $23.00 9) Laborer Group 7 $12.00 $18.00 $24.00 10) Laborer Group 8 $12.50 $18.75 $25.00In cell C1 enter the formula:

=SUMPRODUCT(--($A$3:$A$10=$A$1),--($B$3:$B$10=$B$1),(C3:C10))

Then drag it right for cells D1 & E1

See how that works.

MIKE

Yes, it worked like a charm. Thank you so much, Mike!

Ask Your Question

Weekly Poll

Do you think manufacturers should do more to reduce phone and tablet usage among kids?

Discuss in The Lounge

Poll History