Tuesday, July 2, 2013

Google Spreadsheet Function Match

Question:

I am looking for the proper formula to verify data from a single column in one sheet, compare it to the next sheet for matches and when a match is found report as such in a seperate cell of that sheet. Basic example, Column A has employee names on all sheets, whenever the next sheet is updated I want to know if the person has appeared on a prior sheet and have that post to column b as Repeat: Yes. Any help would be greatly appreciated.


Solution:

Now, suppose we have the following data in 'Sheet1':


And if you want to check in 'Sheet2' whether the name already exists in Column A of 'Sheet1', then put the following formula in Cell B1 of 'Sheet2':

=arrayformula(if(row(A:A)=1;"Reapeat";if(A:A="";"";iferror(if(match(A:A;'Sheet1'!A:A;0);"Yes";"No");"No"))))


So now, as you can see in the screenshot above of 'Sheet2', you will have "Yes" in Column B, if the name in Column A of 'Sheet2' already exists in Column A of 'Sheet1'.

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.
I also take up private and confidential projects:

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html

Thanks,

No comments:

Post a Comment