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.
MRG_ALAsked:
Who is Participating?
 
asiduCommented:
A sample file attached. You can modify it as per your need.
Work1.xls
0
 
KnutsonBMCommented:
vlookup sounds like the way to go as long as the ID is exactly the same

=vlookup(A1,'Sheet1'!A:A,1,false)
0
 
ragnarok89Commented:
ID field on sheet 2 should have

=if(vlookup(A1,Sheet1!A:A,2,0)="done",1,"")

this assumes sheet 2 has COL A = ID, sheet 1 Col A=ID, sheet1 col B=status
0
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..
0
 
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
0
 
Eric ZwiekhorstSAP Business ConsultantCommented:
I put the formula to work in a excel file  here attached,

Kind regards

eric
Sumproduct-example.xls
0
 
MRG_ALAuthor Commented:
Thank you!!!
0
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.