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 Google should sell budget phones in the US?

Discuss in The Lounge

Poll History