Many,many thanks Pete

Q2 - your suggestions of adjusting the table range where you have duplicates
and
sequentially number duplicates can you give me more info on how to do these
options? the data is fairly large - more than 3000 records are your
suggestions still workable with the amount of data? (im using Office 2003)

"Pete_UK" wrote:

Generally for Q1 you would have a formula like:

=IF(ISNA(vlookup_1),IF(ISNA(vlookup_2),"not
present",vlookup_2),vlookup_1)

where vlookup_1 and vlookup_2 are your VLOOKUP formulae looking at
table_1 and table_2 respectively. Basically the formula checks if the
value is in table_1 (and returns normally if it is), and if not in
table_1 it then looks in table_2 automatically, returning the message
if the value is not in either table.

Q2: VLOOKUP (along with MATCH) will only return the first occurence of
multiple matches. However, there are ways of overcoming this - one way
would be to adjust the table range where you have duplicates, another
way would be to sequentially number duplicates (so they are not
duplicates), and there are other approaches too.

Hope this helps.

Pete

On Jan 24, 10:26 am, vlookupabyss
wrote:
Hi

I have two VLookup Questions for assistance please:

1) Is it possible to use an if then logical test with vlookup to check two
seperate workbooks whether the common data is there eg if the vlookup common
data is not found on one report automatically check a second report for the
data to return

( i am trying to create a single report from 2 separate reports - actually
its 3 reports , one report will have a list of employees with their empnumber
the other two reports will both have empnumber and different data that needs
to be combined into one report)

2) Is it possible to utilize VLookup to return ALL values when it matches a
record and not just the first one - eg one empnumber on one report matches on
another report via Vlookup - i want to return the 3rd column data that
contains 4 separate records for the specific empnumber - is it possible to
get all 4 records returned or am i stuck with the first one it matches from
the 3rd column?

I have been told both question 1 and 2 cannot be done the way i would like
to do it so i thought id ask the experts and find out

Thanks