Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup from multiple workbooks
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup from multiple workbooks
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup from multiple workbooks
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup from multiple workbooks
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 On Jan 24, 11:35*am, vlookupabyss wrote: 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- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup from multiple workbooks
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Updating Workbooks from multiple links Workbooks | Excel Worksheet Functions | |||
vlookup multiple workbooks | Excel Discussion (Misc queries) | |||
How do I use VLOOKUP to ref multiple workbooks with multiple tabs? | Excel Discussion (Misc queries) | |||
Vlookup on multiple workbooks | Excel Discussion (Misc queries) | |||
Vlookup in Multiple Workbooks | Excel Discussion (Misc queries) |