#1. Without knowing how your worksheets are set up, you'll need separate
formulas that point to the other sheets.

If you have a cell that contains the worksheet name/customer name, you can embed
that into the =vlookup() formula using =indirect().

Say you have sheet names in row B1:E1 and you want to retrieve the value from
the sheet name in D1, you could use a formula like:

=VLOOKUP(A2,INDIRECT("'"&D$1&"'!a:e"),3,FALSE)

#2. =vlookup() will return the first match that it finds. You may be able to
use data|filter|autofilter to see the data when you have multiple results to
find.

If I want all the results to show up in a single cell, I use a user defined
function:


http://groups.google.co.uk/group/mic...28f1ba868980a8

or

http://snipurl.com/i7q1

The values come back separated by commas.

(There are instructions in that link that tell how to use it.)

Alec H wrote:

Right, I now have vlookup working on the workbook (thanks Dave), however
this has now created 2 further problems for me......

1. How do I get vlookup to check several worksheets within a workbook
for results, at the moment I can only get the table array to look at 1
worksheet per vlookup cell. ie each of my customers is on a different
worksheet.

2. How do I configure my "results" sheet to list multiple results for a
search? ie Column 1 contains a unique number for each customer, column 2
contains a numeric area code (eg area 23 = SW England) and there may be
several customers in that area. I want to be able to search by either
customer number (vlookup seems to do this ok) OR area number (multiple
possible results)..........



--
Alec H
------------------------------------------------------------------------
Alec H's Profile: http://www.excelforum.com/member.php...o&userid=31042
View this thread: http://www.excelforum.com/showthread...hreadid=507154

--

Dave Peterson