Vlookup from multiple workbooks

[vlookupabyss]

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

 

[Pete_UK]

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

 

[vlookupabyss]

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]

Imagine your lookup values are in column A of Sheet1, occupying
A2:A3000, and that you have some duplicates. In a helper column (eg
column G) you enter this formula in G2:

=A2&"_"&COUNTIF(A$2:A2,A2)

and copy it down. This will now give you a sequential number added on
to each item that is in A, so if you have dog, cat, ape, dog, bird
etc, you will now have dog_1, cat_1, ape_1, dog_2, bird_1 etc, and
instead of the duplicates you now have unique references to each
record. Instead of using Column A as the lookup values, you will use
Column G, but you will probably use INDEX/MATCH instead of VLOOKUP as
this column is no longer at the left hand edge of the table (you could
insert a new column A if you like).

Somewhere else, maybe on a different sheet, you would have the item
you want to bring from the table (suppose you enter this in B2, eg
"dog"), and you would have up to 10 rows below (say, if you have this
number of duplicates) to bring those matching values across. Put the
numbers 1 to 10 in A3:A12, and this formula in B3:

=IF(ISNA(MATCH(B$2&"_"&$A3,Sheet1!$G$2:$G$3000,0)),"",INDEX(Sheet1!B
$2:B$3000,MATCH(B$2&"_"&$A3,Sheet1!$G$2:$G$3000,0)))

and copy down to B12 - this will bring matching values from the
duplicates (or return ""). You could copy across to column F to bring
across other fields.

Hope this helps.

Pete

 

[Herbert Seidenberg]

This approach combines the three sheets,
using Pivot Table > Multiple Consolidation Ranges,
and then translates the Pivot Table into an
alphanumeric List with VBA.
Excel 2003 or later.
http://www.freefilehosting.net/download/3b1kh