Saturday, August 3, 2013

Single Arrayformula Vlookup For Multiple Columns


( by Sandy )

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

Email                            Name       Class         John1            1b         John2            2b         John3            3b         John4            4b

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

Email                            Name       Class         John1            1b         John2            5b         John3            6b         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


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: 


1 comment: