The 0 tells the formula you want an exact match. What you're trying to match
is the number 1. So, 0 means find an exact match of the number 1.

So, you might be thinking: Yeah, right! Where does the number 1 come into
play?

MATCH(1,(A1:A5=A8)*(B1:B5=B8)*(C1:C5=C8),0)

Here's where the number 1 comes into play (or not!):

(A1:A5=A8)*(B1:B5=B8)*(C1:C5=C8)

Each of those logical statements will evaluate to either TRUE or FALSE.
Those results are then multiplied together to produce an array of either 1
or 0. Like this:

A1=A8 = False * B1=B8 = FALSE * C1=C8 = False
FALSE*FALSE*FALSE = 0

Only where *all 3* conditions are TRUE will you get a result of 1:

A2=A8 = TRUE * B2=B8 = TRUE * C2=C8 = TRUE
TRUE*TRUE*TRUE = 1

So, there's the 1 and we want an exact match of 1.

If there are no 1's to match that's when you get the error. To trap that
error and return something else in its place try it like this (array
entered):

=IF(ISNA(MATCH(1,(A1:A5=A8)*(B1:B5=B8)*(C1:C5=C8), 0)),"record not
found",INDEX(D1:D5,MATCH(1,(A1:A5=A8)*(B1:B5=B8)*( C1:C5=C8),0)))


--
Biff
Microsoft Excel MVP


wrote in message
...
Wow. Many thanks - works a treat. Just one question, I can't figure
out why I need the numbers 1 and 0 in the formula too. I thought 0 was
what would be return in the event no record was found and tried
changing this to a custom message such as "record not found" but no
luck.