vlookup

[lordguharoy]

hii i have a problem please some body help me out:

I have datas in 2 sheets:

sheet 1 sheet 2

Name number Name number

a 1235 a 1235
b 5689 b 5689
c 9876 c 9876
d 9876 d 9879
e 8796 e 8796

the names and numbers are same in both the sheets except name d is entered wrongly in sheet 2(9879).my question is how can i use v lookup to determine that the name d in sheet 2 has been entered wrongly, suppose its a huge sheet with many names and numbers so its impossible to track with naked eye.please somebody help me out

 

[sheetspread]

If the numbers are always correct you can lookup to the left using index/match, otherwise fuzzyvlookup is what you need.

 

[lordguharoy]

sir actually how will i evaluate in sheet 2 that all the numbers entered manually in sheet 2 under the respective names are correct or not. sir dont you think it can only be determined with a v lookup formulae in the next column to the numbers in sheet 2 so that the number which is being entered wrongly under the name d(9879) will be detected as an error.sir please give me an example of the formulae you are suggesting.

 

[sheetspread]

At first it seemed like the numbers were exact but not the names, but if it's the other way around then vlookup will work (true, you don't need the fuzzy version just to identify via an error result)

If your data (no headers) starts in A1 on both sheets paste this formula into C1 on sheet 2 and copy down:

=IF(VLOOKUP(A1,Sheet1!$A$1:$B$5,2,FALSE)=B1,"","DOESN'T MATCH")

 

[markmzz]

Or try this (array formula - use Ctrl+Shift+Enter and not only Enter):

Note: VERDADEIRO = TRUE and FALSO = FALSE.

Excel 2007 <TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center">a</TD><TD style="TEXT-ALIGN: center">1235</TD><TD style="TEXT-ALIGN: center">a</TD><TD style="TEXT-ALIGN: center">1235</TD><TD style="TEXT-ALIGN: center">VERDADEIRO</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: center">b</TD><TD style="TEXT-ALIGN: center">5689</TD><TD style="TEXT-ALIGN: center">b</TD><TD style="TEXT-ALIGN: center">5689</TD><TD style="TEXT-ALIGN: center">VERDADEIRO</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: center">c</TD><TD style="TEXT-ALIGN: center">9876</TD><TD style="TEXT-ALIGN: center">c</TD><TD style="TEXT-ALIGN: center">9876</TD><TD style="TEXT-ALIGN: center">VERDADEIRO</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: center">d</TD><TD style="TEXT-ALIGN: center">9876</TD><TD style="TEXT-ALIGN: center">d</TD><TD style="TEXT-ALIGN: center">9879</TD><TD style="TEXT-ALIGN: center">FALSO</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: center">e</TD><TD style="TEXT-ALIGN: center">8796</TD><TD style="TEXT-ALIGN: center">e</TD><TD style="TEXT-ALIGN: center">8796</TD><TD style="TEXT-ALIGN: center">VERDADEIRO</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: center">******</TD><TD style="TEXT-ALIGN: center">******</TD><TD style="TEXT-ALIGN: center">******</TD><TD style="TEXT-ALIGN: center">******</TD><TD style="TEXT-ALIGN: center">******</TD></TR></TBODY></TABLE>Sheet6


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Array Formulas <TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>E1</TH><TD style="TEXT-ALIGN: left">{=ISNUMBER(MATCH(C1&D1,A$1:A$5&B$1:B$5,0))}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

</TD></TR></TBODY></TABLE>

For two sheets use this:

=ISNUMBER(MATCH(C1&D1,'Sheet 1'!A$1:A$5&'Sheet 1'!B$1:B$5,0))

Markmzz <!-- / message -->

 

[lordguharoy]

thank you both of you it was a great help