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.

Many thanks again - you have saved me many hours of toil with your
straighforward and clear explanations - works brilliantly and now I
have a new weapon in my formula arsenal!

:-)

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


wrote in message
...
Many thanks again - you have saved me many hours of toil with your
straighforward and clear explanations - works brilliantly and now I
have a new weapon in my formula arsenal!

:-)

Ok, now I'm pushing my luck - I know so if I don't get any response I
won't be offended. But.

One last thing I need to do is get information from an Excel file
(always with the same name and range) but the file may reside in
different locations depending upon the students username. ie. file
could be called 'spreadsheet.xls and the range A1:A6' but each student
would have their own copy meaning there would be one in \\servername
\serverpath\11jsmith\ and another in \\servername\serverpath\11djones\
etc. The server pathnames would all be stored in a spreadsheet to
which I need the information returned.

If the file has a static location, doing a Vlookup is easy for this
kind of task. But a Vlookup where the file location depends upon a
cell in my spreadsheet - I can't see how that would work.

I am very grateful in advance for any help anyone can provide

Excel doesn't make this easy!

At this link look for a thread titled: VLOOKUP with INDIRECT

http://www.microsoft.com/communities...xp=&sloc=en-us


--
Biff
Microsoft Excel MVP


wrote in message
...
Ok, now I'm pushing my luck - I know so if I don't get any response I
won't be offended. But.

One last thing I need to do is get information from an Excel file
(always with the same name and range) but the file may reside in
different locations depending upon the students username. ie. file
could be called 'spreadsheet.xls and the range A1:A6' but each student
would have their own copy meaning there would be one in \\servername
\serverpath\11jsmith\ and another in \\servername\serverpath\11djones\
etc. The server pathnames would all be stored in a spreadsheet to
which I need the information returned.

If the file has a static location, doing a Vlookup is easy for this
kind of task. But a Vlookup where the file location depends upon a
cell in my spreadsheet - I can't see how that would work.

I am very grateful in advance for any help anyone can provide

On Mar 13, 6:41*pm, "T. Valko" wrote:
Excel doesn't make this easy!

At this link look for a thread titled: VLOOKUP with INDIRECT

http://www.microsoft.com/communities...efault.aspx?qu...

--
Biff
Microsoft Excel MVP

wrote in message

...



Ok, now I'm pushing my luck - I know so if I don't get any response I
won't be offended. But.

One last thing I need to do is get information from an Excel file
(always with the same name and range) but the file may reside in
different locations depending upon the students username. ie. file
could be called 'spreadsheet.xls and the range A1:A6' but each student
would have their own copy meaning there would be one in \\servername
\serverpath\11jsmith\ and another in \\servername\serverpath\11djones\
etc. The server pathnames would all be stored in a spreadsheet to
which I need the information returned.

If the file has a static location, doing a Vlookup is easy for this
kind of task. But a Vlookup where the file location depends upon a
cell in my spreadsheet - I can't see how that would work.

I am very grateful in advance for any help anyone can provide- Hide quoted text -

- Show quoted text -

Many thanks again - will take a look