VLOOKUP: 2 Conditions

[almouchie]

I have this workbook,

I want to compare data from sheet1 to sheet 2
If the data is the same in sheet2 to write CORRECT, if not to write which of the two is wrong
in sheet 1 I have in column A, the client name
& in column B data of novemeber & in column C the data of december
the same is sheet 2

what I want is a check test
that compares sheet 2 to sheet 1 & if the data in both nov & dec is the same to write correct,
if it's different write which data is wrong whether the november,or the december


I attached an example of the book
thanks in advance

[XLGibbs]

Check this out. You can use a couple of methods as in the attached.

A combination i think is best

In column C dragged down (ranges to be adjusted and equally sized to your data)
=SUMPRODUCT(--(Sheet2!$A$2:$A$5=Sheet1!A2),--(Sheet2!$B$2:$B$5=Sheet1!B2),--(Sheet2!$C$2:$C$5=Sheet1!C2))

in Column D dragged down
=IF(D2<>1,IF(VLOOKUP(A2,Sheet2!$A$2:$B$5,2,FALSE)=B2,"December","November") ,"Correct")


The attached demonstrates using your sample...First evaluate if the row has matching data, the Vlookup then determines when there is no match, which month is off...you may be able to apply either if these examples, and may be able to use the AND function in a VLOOKUP = scenario..

Hope it helps

[almouchie]

Thanks Gibbs for ur reply

I used the formulas u gave me & after adjusting to fit my original sheet, it worked out fine

I have a question its more about the formula
what does the 2 minus signs in the sumproduct mean ? --
another when i applied the formula to my original sheet it gave me an answer 2 for difference in december & zero for difference in november figures &1 for when its correct.
how did the formula write the 0,1 & 2
on what basis

btw is it doable in one formula instead of having 2 columns
Thanks again

[mdmackillop]

I have a question its more about the formula
what does the 2 minus signs in the sumproduct mean ? --

This question was discussed here.
http://www.vbaexpress.com/forum/show...ighlight=unary

[XLGibbs]

Well, you can use more nested VLOOKUPS, but it is trickier. My take was to simplify the process by first eliminating the corrects before comparing December and November.

I am not sure how it came up with a 2 at all unless you had a duplicate critera...but the SUMPRODUCT formula will Sum the total of the PRODUCT of each of its homogenous elements.

So in my example we had a range of $A$2:$A$5 = A2 (for the 2 letter code column)

so the array inside the formula would be as follows if the 2nd part of the array evaluated as a postive match:
{FALSE,TRUE,FALSE,FALSE}

The -- coerces the boolean results into numerics like this:
{0,1,0,0}

So with the conditions of the sumproduct having 3 similar arrays

it would appear something like this
SUMPRODUCT({0,1,0,0},{0,1,0,0},{0,1,0,0} )

which broken down by element position would be
0 * 0 * 0 = 0
1* 1 * 1 = 1
0* 0 * 0 = 0
0* 0* 0= 0

The sum of each arrays resulting product = 1 which means the 2nd part of the range $A$2:$A$5 met all 3 conditions.

Since the critera (of = A2, =B2, = C2) are relative, but the range is anchored ($A$2:A$5)

Hope that helps explain it

for a one column alternative:

=IF(VLOOKUP(A2,Sheet2!$A$2:$B$5,2,FALSE)<>B2,"November",IF(VLOOKUP(A2,Sheet 2!$A$2:$C$5,3,False)<>C2,"December","Correct"))

May work... basically If the 2nd column in the row of the table matching the CODE in A2 does not match sheet1 November, November does not match. If it does match, evaluate December, if it does not match..."December", otherwise both match and it returns "Correct"