Compare Sheet and Columns

[doku]

I am not sure if its a duplicate post.
I tried searching the forum, but couldn't relate it to the scenario I needed.

Book1
	A	B	C	D
1	Sheet1
2	Name	Age
3	a	10
4	b	20
5	c	30
6	d	40
7	e	50
8
9	Sheet2
10	Name	Age
11	b	20
12	c	30
13	d	40
14
15	Sheet3
16	Name	Age
17	a	notfound
18	b	matched
19	c	matched
20	d	didntmatch
21	e	notfound
Sheet1

I have three sheet. named Sheet1, Sheet2 and Sheet3.
Sheet1 being the master sheet. What I want to achieve is:
I want to search for Name in sheet1 with the list of names in Sheet2,
If the name is found, then compare the Age in Sheet1 and Sheet2. If the age matches make an entry in Sheet3 "matched" else "didnt match".
If the name itself is not found on sheet2 , make an entry saying not found.
I am not sure what would be the best approach to solve the problem(formula/VB)...
Hope I am clear with my query...
Can you all please help ?

 

[doku]

Correction:
Age of d in Sheet2 should not be 40... say.. 41

 

[jindon]

Is there any possibility like Same name with different ages, one matched and one doesn't match....

 

[doku]

No.... The name column will always be unique.....

 

[jindon]

Sub test()
Dim a, b, i as Long, w(), e
a = Sheets("sheet1").range("a1").currentregion.resize(,2).value
With createobject("scripting.dictionary")
    .comparemode = vbtextcompare
    For i = 2 To ubound(a, 1)
        .item(a(i, 1)) = Array(a(i, 2), i)
    Next
    b = Sheets("sheet2").range("a1").currentregion.resize(, 2).value
    For i = 2 to UBound(b, 1)
        If .exists(b(i, 1)) then
            w = .item(b(i, 1))
            a(w(1), 2) = IIf(w(0) <> b(i, 2),"not ","") & "matched
            .remove b(i, 1)
        End if
    Next
    if .count > 0 then
        for each e in .items
            a(e(1), 2) = "not found"
        next
    end if
End with
Sheets("sheet3").cells(1).resize(ubound(a,1), 2).value = a
End Sub

 

[mfexcel]

In Sheet 3 Cell B2, type this formula (Where A2 contains the name)
=IF(ISERROR(IF(VLOOKUP(A2,Sheet1!A:B,2,FALSE)=VLOOKUP(A2,Sheet2!A:B,2,FALSE),"matched","didn't match")),"not found",IF(VLOOKUP(A2,Sheet1!A:B,2,FALSE)=VLOOKUP(A2,Sheet2!A:B,2,FALSE),"matched","didn't match"))
Copy Down

If you accept #N/A as "not found". you may simply the formula as
=IF(VLOOKUP(A2,Sheet1!A:B,2,FALSE)=VLOOKUP(A2,Sheet2!A:B,2,FALSE),"matched","didn't match")

 

[doku]

Wow!!! Works Perfect !!!!
Thanks a Million !!!

 

[doku]

I checked the formula too .... works great....
Just trying to figureout the formula and its working !!!

The only thing that the formula is unable to achieve is the name column....
Thanks !!!

 

[mfexcel]

what do you mean by name column?
btw, the logic is simple:
Lookup the value (name) of Sheet 3 in both Sheet1 and Sheet2. If the name is not avaliable in either Sheet1 or Sheet2, it returns #N/A
If both Sheet1 and Sheet2 contain the same name, the Age(lookup value) is compared and corresponding result returns

 

[doku]

Yes.... I got that....
The only confusion that I have now is....
If I want to compare a column index 5 in sheet1 and column index 8 in sheet2 and the result to be palced in column index 5 in sheet3.
Is that possible ?