Try this:

=SUMPRODUCT(--(X1:X5=A1),--(Y1:Y5=B1),Z1:Z5)

--
Biff
Microsoft Excel MVP


"JK494" wrote in message
...
Help,
I am trying to create a spreadsheet that numpties can use only to be
stumped myself!

I want to look up a value in column X and Y show column Z, but only if
column B and C are both correct. ie if b = engineer and c= upper then it
needs to look for engineer in X and upper in Y and display the rate in Z

Columns B&C i will use as a drop down selection box to get the correct
titles and grade.

X Y Z
Engineer Upper £3
Engineer Middle £2
Engineer Lower £1
Foreman Upper £1.4
Foreman Lower £1.2