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:

http://igoogledrive.blogspot.com/p/paid-projects.html

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,

Kishan.

iGoogleDrive