Help!

[kimknguyen]

Help! I have a big table with names (6000 records) and a smaller table (384 record) with names. How do I identify which names on the BIG table are on the SMALL table? Thanks

 

[AlanSidman]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

This will help to identify your layout and provide you with a viable solution.

Remember to desensitize the data.

 

[kimknguyen]

Thank you AlanSidman for the reminder. Attached is the file.
Thank you for your help.
kim

 

[Fluff13]

How about, in D5 copied down
=IFERROR(INDEX(Table3[Date Back],MATCH(B5,Table3[Name],0)),"")

 

[AlanSidman]

An alternative solution is to use Power Query and join the two tables on the name. Attached is the file with that solution.

 

[kimknguyen]

How about, in D5 copied down
=IFERROR(INDEX(Table3[Date Back],MATCH(B5,Table3[Name],0)),"")

Hi Fluff13,
It works! (I tried to figure it out for a few hours, but had to give up and asked for help).
Thank you so much,
kim

 

[kimknguyen]

An alternative solution is to use Power Query and join the two tables on the name. Attached is the file with that solution.

Hi Alandsidman,
I am not familiar with Power Query (which is another thing I have to learn). But I tried Fluff13's solution and it works.
Thanks for your help.
kim

 

[Fluff13]

You're welcome & thanks for the feedback

 

[AlanSidman]

Suggest you purchase the book M is for Data Monkey by Ken Puls and Miguel Escobar

 

[Peter Bartholomew]

At the moment, an approximate match is much faster than an exact match applied to a sorted list, so
=SIGN(LOOKUP([@Name],RefTable[Name])=[@Name])
would provide a more efficient test. The date itself may then be returned by
=IF([Match?],LOOKUP([Name],RefTable[Name],RefTable[Date Back]),"")

I think this is about to change in Office 365, with the lookup table being sorted within memory to allow a bisection search. In that case Fluff13's solution becomes the preferred option.

Opinions, anyone?