Lookup in excel

I have an excel file which contains two worksheets. The first worksheet has a column called ID and a field called Status. If there is an ID on this worksheet (Sheet1) and the Status = "Done" then I want the ID field on Sheet 2 which for the same ID to be updated to a 1 in order to count the total number done. Sheet 2 contains all of the ID's which could potentially be upwards of 20k but sheet 1 only has the done id's which will be added to on a daily basis.

If I could get this to update automatically that would be great although I don't know the best way to go. Thank you.
Who is Participating?
A sample file attached. You can modify it as per your need.
vlookup sounds like the way to go as long as the ID is exactly the same

ID field on sheet 2 should have


this assumes sheet 2 has COL A = ID, sheet 1 Col A=ID, sheet1 col B=status
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Eric ZwiekhorstSAP Business ConsultantCommented:
it would work with a sumproduct as well
=SUMPRODUCT((A1:A50000 = G1)*(B1:B50000="done"))

where A1:A50000 is the column with the ID's, G1 holds the ID  you want to check for and B1:B50000 is the column whre done is to be in..
Eric ZwiekhorstSAP Business ConsultantCommented:
By the way, the sumproduct does the count of how many time you find done for a certain ID...

Kind regards

Eric ZwiekhorstSAP Business ConsultantCommented:
I put the formula to work in a excel file  here attached,

Kind regards

MRG_ALAuthor Commented:
Thank you!!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.