Lookup and Compare

Attached is a workbook that has two lists with about the same data.  Given that they are not a one for one on the same row, I need to do a "lookup" and compare the values, relating to columns A & G with the values found in columns D and J.  

Example: If Column A8 and G? are the same, then look at D8 and J? to see if they are comparable.  If the values are different, then that needs to be shown somehow, or there could be a True/ False next to the values in column D, if that would be best.

What is the best way to perform a lookup and compare?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
I believe this is what you are looking for??

Missus Miss_SellaneusCommented:
Paste this into E3 and copy down:

Cook09Author Commented:
Hi Experts,
Initially I liked both solutions,  When using Saurabh's, initially there appeared a #REF error, every time it was used.   As we have 27000 rows, with 20 columns, and this formula needed to be in two of the columns, it just wouldn't work.  Even though Missus's was used,  in our meeting, it was discovered that the formula was not returning the correct values.

In trying to use Saurabh's again, it was discovered that some of the fields had an #N/A located within it, and that's what was causing the #REF for every cell that it was in, >27000.  After correcting those, then the formula seemed to work. However, it occurred in row 12510, and with no way of knowing what was really causing this error.  Any way of being able to isolate why it is going into Error, human input or other?

In the VLOOKUP formula, isn't the default, for the formula, "False?" I thought so until it was providing incorrect data, and when a False was added to it, then it seems to be pulling back the correct information.  Any thoughts?

Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Saurabh Singh TeotiaCommented:

In the row where it gives you a #ref error..can i see..because this formula will give you an error that too #ref error when it finds that error in the range where you are evaluating..so i need to see what's happening..

Also you are right if you applying the vlookup formula and for exact match you need to add false their to see the correct answer so what you need to do is this...


I did couple of tweaks in Sellanus formula to make it more effective and efficient...


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

I'm not 100% sure if I understand your problem - but here's a solution which checks both columns and returns true/false. See column E.
Cook09Author Commented:
All three solutions did eventually work.  The only issue with dabug80 was the time it took to copy down 27,000 rows.  Had to wait a little longer than wanted, but it did produce the same results as the others, and provides another option. I like the Trim added with the VLOOKUP, and was able to get the SUMPRODUCT to work.

Thanks Experts....
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.