Matching Data, then pulling data through. Without using VB

[rudeolf]

Hi,

Would anyone know the best way or formula to complete the following:

1 unique identifier, 2 worksheets. First objective is to see if the unique identifier from worksheet 1 appears anywhere in worksheet 2. To display "Yes" or "No"

Second objective, if it does appear, then pull the info from worksheet 2 into worksheet1.

I have tried multiple vlookup and isna(vlookup) but am always getting a "#NA" value. When I have done a manual find, the data is there.

I have created tables in all worksheets.

There is about 50,000 records to match / search / migrate.

The unique identifier is the COMPANY ID CODE.

Sheet1
"Table1"

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Company ID Code
[/TD]
[TD]Local ID Code
[/TD]
[TD]Date Started
[/TD]
[TD]Site
[/TD]
[/TR]
[TR]
[TD]123456789
[/TD]
[TD]ABCD1
[/TD]
[TD]01/01/2016
[/TD]
[TD]London
[/TD]
[/TR]
[TR]
[TD]987654321
[/TD]
[TD]XYZ12
[/TD]
[TD]01/02/2016
[/TD]
[TD]Barcelona
[/TD]
[/TR]
[TR]
[TD]111222333
[/TD]
[TD]JKLM2
[/TD]
[TD]01/03/2016
[/TD]
[TD]Venice
[/TD]
[/TR]
[TR]
[TD]999888777
[/TD]
[TD]HIJK1
[/TD]
[TD]01/04/2016
[/TD]
[TD]New York
[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2
"Table2"

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Company ID Code
[/TD]
[TD]Date Started
[/TD]
[TD]Site
[/TD]
[TD]Training Complete?
[/TD]
[/TR]
[TR]
[TD]999888777
[/TD]
[TD]01/04/2016
[/TD]
[TD]New York
[/TD]
[TD]Y
[/TD]
[/TR]
[TR]
[TD]111222333
[/TD]
[TD]01/03/2016
[/TD]
[TD]Venice
[/TD]
[TD]N
[/TD]
[/TR]
[TR]
[TD]123456789
[/TD]
[TD]01/02/2016
[/TD]
[TD]London
[/TD]
[TD]Y
[/TD]
[/TR]
[TR]
[TD]010203040
[/TD]
[TD]01/11/2016
[/TD]
[TD]Sydney
[/TD]
[TD]N
[/TD]
[/TR]
</tbody>[/TABLE]

Firstly, I would like to see the following:

Sheet1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Company ID Code
[/TD]
[TD]Local ID code
[/TD]
[TD]Date Started
[/TD]
[TD]Site
[/TD]
[TD]Appears on Training List?
[/TD]
[/TR]
[TR]
[TD]123456789
[/TD]
[TD]ABCD1
[/TD]
[TD]01/01/2016
[/TD]
[TD]London
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]987654321
[/TD]
[TD]XYZ12
[/TD]
[TD]01/02/2016
[/TD]
[TD]Barcelona
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]111222333
[/TD]
[TD]JKLM2
[/TD]
[TD]01/03/2016
[/TD]
[TD]Venice
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]999888777
[/TD]
[TD]HIJK1
[/TD]
[TD]01/04/2016
[/TD]
[TD]New York
[/TD]
[TD]Yes
[/TD]
[/TR]
</tbody>[/TABLE]

Then i want it to show:
Sheet1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Company ID code
[/TD]
[TD]Local ID Code
[/TD]
[TD]Date Started
[/TD]
[TD]Site
[/TD]
[TD]Appearing on training list?
[/TD]
[TD]Date started (as recorded locally)
[/TD]
[TD]Training Complete?
[/TD]
[/TR]
[TR]
[TD]123456789
[/TD]
[TD][/TD]
[TD]01/01/16
[/TD]
[TD]London
[/TD]
[TD]Yes
[/TD]
[TD]01/02/16
[/TD]
[TD]Y
[/TD]
[/TR]
[TR]
[TD]987654321
[/TD]
[TD][/TD]
[TD]01/02/16
[/TD]
[TD]Barcelona
[/TD]
[TD]No
[/TD]
[TD]#N/A
[/TD]
[TD]#N/A
[/TD]
[/TR]
[TR]
[TD]111222333
[/TD]
[TD][/TD]
[TD]01/03/16
[/TD]
[TD]Venice
[/TD]
[TD]Yes
[/TD]
[TD]01/03/16
[/TD]
[TD]N
[/TD]
[/TR]
[TR]
[TD]999888777
[/TD]
[TD][/TD]
[TD]01/04/16
[/TD]
[TD]New York
[/TD]
[TD]Yes
[/TD]
[TD]01/04/16
[/TD]
[TD]Y
[/TD]
[/TR]
</tbody>[/TABLE]

Any help is appreciated. I understand that I will get null or N/A values for Company ID codes that are not appearing in Sheet2 Table2.

Any help is appreciated.

Thanks.

 

[steve the fish]

If you cant find a value with vlookup that you believe you have found using find then you probably have an issue with some numbers being text and others being true numbers.

 

[godsaaint]

try the formulas bellows. If they still don't work you'll have to check the formatting.

=IF(COUNTIF(Table2[Company ID Code],[Company ID Code]),"Yes","No")
=INDEX(Table2[Date Started],MATCH([@[Company ID Code]],Table2[Company ID Code],0))
=INDEX(Table2[Training Complete?],MATCH([@[Company ID Code]],Table2[Company ID Code],0))

 

[rudeolf]

Hi,
Thanks for the formulas. The CountIF worked great. Everyone keeps going on about vlookups so its the first thing i always try.

Any advice for the second part of my question?

As now i have identified Yes and No > I will filter to display only "Yes". All with YEs need the info from the other table. I presume this is a vlookup formula?

 

[godsaaint]

Hi,
Thanks for the formulas. The CountIF worked great. Everyone keeps going on about vlookups so its the first thing i always try.

Any advice for the second part of my question?

As now i have identified Yes and No > I will filter to display only "Yes". All with YEs need the info from the other table. I presume this is a vlookup formula?

In my first post I included the two additional formulas needed to extract the date and if the training was completed.

Insert in "Date Started" column: INDEX(Table2[Date Started],MATCH([@[Company ID Code]],Table2[Company ID Code],0))
Insert in "Training Complete?" column: INDEX(Table2[Training Complete?],MATCH([@[Company ID Code]],Table2[Company ID Code],0))