Using Vlookup to compare 2 columns of data

[TomTTT]

Hi everyone,

I'm having difficulties using vlookup to compare two columns of TEXT data. My goal is find out how many of items in the "Eligible Serial Numbers" list have been ordered for destruction. The items listed for destruction are listed in the "Serial Numbers (destroyed).

Whenever I put in a VLOOKUP, I get a serial number that doesn't make sense to me. For example, if I want to find out if "362351581" from the Eligible List appears on the destroyed list. What do you suggest?</SPAN>

[TABLE="width: 443"]
<TBODY>[TR]
[TD][U]Destroyed? (i.e. Vlookup formula in this column)[/U]</SPAN>
[/TD]
[TD]</SPAN>Eligible Serial Numbers
[/TD]
[TD]Serial Numbers (destroyed)</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]362351581</SPAN>
[/TD]
[TD]362351581</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]362351582</SPAN>
[/TD]
[TD]362351582</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]362351584</SPAN>
[/TD]
[TD]362351584</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]362351585</SPAN>
[/TD]
[TD]362351585</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]362351590</SPAN>
[/TD]
[TD]362351590</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]362351603</SPAN>
[/TD]
[TD]362351603</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]362351604</SPAN>
[/TD]
[TD]362351604</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]362351605</SPAN>
[/TD]
[TD]362351605</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]362351611</SPAN>
[/TD]
[TD]362351611</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]362351612</SPAN>
[/TD]
[TD]362351612</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]362351617</SPAN>
[/TD]
[TD]99379006</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]362351618</SPAN>
[/TD]
[TD]99379007</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]362351619</SPAN>
[/TD]
[TD]142353015</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]362351620</SPAN>
[/TD]
[TD]142353016</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[TomTTT]

Complementing my previous post
Take a look at the Help file and look for MATCH
You see that it doesn't return the value itself, it returns the relative position of the value.

For example
A1:A3
John
Mary
Mike

=MATCH("Mary",A1:A3,0)
returns 2

But if you try
=MATCH("Richard",A1:A3,0)
it returns #N/A

M.

I have uploaded a selection of the file. Can you take a look at it? I'm frustrated here thinking I'm missing something obvious.

Link: https://www.files2u.com/fastftp/GET/3PD7XZJLARO8X290
[SIZE=-1][SIZE=-1]PIN to download file: 109081[/SIZE][/SIZE]
(note: the link expires in 48 hours)

 

[Marcelo Branco]

I have uploaded a selection of the file. Can you take a look at it? I'm frustrated here thinking I'm missing something obvious.

Link: https://www.files2u.com/fastftp/GET/3PD7XZJLARO8X290
[SIZE=-1][SIZE=-1]PIN to download file: 109081[/SIZE][/SIZE]
(note: the link expires in 48 hours)

I downloaded your file.
You should not have changed ISNUMBER to ISTEXT (see #9 and #10)

Try this in A2
=IF(ISNUMBER(MATCH(C2,$B$2:$B$107971,0)),"Yes","No")
copy down

M.

 

[TomTTT]

I downloaded your file.
You should not have changed ISNUMBER to ISTEXT (see #9 and #10)

Try this in A2
=IF(ISNUMBER(MATCH(C2,$B$2:$B$107971,0)),"Yes","No")
copy down

M.

Thank you so much for your help!