Saturday, August 3, 2013

Single Arrayformula Vlookup For Multiple Columns

Question:

( by Sandy )



Hello,
I need help :)
I need to compare  two different sheets.
In first sheet I have database, for example

Email                            Name       Class
em1@email.com         John1            1b
em2@email.com         John2            2b
em3@email.com         John3            3b
em4@email.com         John4            4b

In another sheet I have data from form, which fills teachers.

Email                            Name       Class
em1@email.com         John1            1b
em2@email.com         John2            5b
em3@email.com         John3            6b
em4@email.com         John4            4b

I need to check do they fill correctly. Do email mach name and class from database
 I use vlookup =arrayformula(VLOOKUP(A1,sheet!$A$1:$C,1,false)) this formula return if email match but I need that all three rows match, then its true, other way (for example mach email, name but do not match class) show error
Maybe I use wrong formula
Many thanks

Solution:


Have a look at the following screenshot of 'sheet' of my Spreadsheet, that is data sheet:



Have a look at the following screenshot of 'Form Responses' of my Spreadsheet:



I have the following formula in Cell D1:
=arrayformula(if(row(A:A)=1;"Correct / Incorrect";if(A:A="";"";if( (vlookup(A:A;'sheet'!A:B;{2}*sign(row(A:A));false)=B:B) * (vlookup(A:A;'sheet'!A:C;{3}*sign(row(A:A));false)=C:C) ;"Correct";"Incorrect"))))

Or you can also have the following formula in Cell D1:
=arrayformula(if(row(A:A)=1;"Correct / Incorrect";if(A:A="";"";if(query(iferror(if(vlookup(A:A;'sheet'!A:C;{2,3}*sign(row(A:A));false)=B:C;1;0));"select Col1*Col2";1);"Correct";"Incorrect"))))


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,

1 comment: