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"

Company ID Code	Local ID Code	Date Started	Site
123456789	ABCD1	01/01/2016	London
987654321	XYZ12	01/02/2016	Barcelona
111222333	JKLM2	01/03/2016	Venice
999888777	HIJK1	01/04/2016	New York

<tbody>
</tbody>

Sheet2
"Table2"

Company ID Code	Date Started	Site	Training Complete?
999888777	01/04/2016	New York	Y
111222333	01/03/2016	Venice	N
123456789	01/02/2016	London	Y
010203040	01/11/2016	Sydney	N

<tbody>
</tbody>

Firstly, I would like to see the following:

Sheet1

Company ID Code	Local ID code	Date Started	Site	Appears on Training List?
123456789	ABCD1	01/01/2016	London	Yes
987654321	XYZ12	01/02/2016	Barcelona	No
111222333	JKLM2	01/03/2016	Venice	Yes
999888777	HIJK1	01/04/2016	New York	Yes

<tbody>
</tbody>

Then i want it to show:
Sheet1

Company ID code	Local ID Code	Date Started	Site	Appearing on training list?	Date started (as recorded locally)	Training Complete?
123456789		01/01/16	London	Yes	01/02/16	Y
987654321		01/02/16	Barcelona	No	#N/A	#N/A
111222333		01/03/16	Venice	Yes	01/03/16	N
999888777		01/04/16	New York	Yes	01/04/16	Y

<tbody>
</tbody>

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))