Using Vlookup to compare 2 columns of data

TomTTT

New Member
Joined
Nov 11, 2011
Messages
8
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>

Code:
[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]
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
To count how many appears on the destroyed list you can use

=SUMPRODUCT(COUNTIF($C$2:$C$15,$B$2:$B$15))

M.
 
Upvote 0
To count how many appears on the destroyed list you can use

=SUMPRODUCT(COUNTIF($C$2:$C$15,$B$2:$B$15))

M.

Hi Marcelo,

Thanks for suggesting the SUMPRODUCT but that will not cover the result I'm going for.
In my example above, I have a "Destroyed?" column on the far left. Since I have a total of around 169,000 rows in this spreadsheet, it is important for me to know which items were destroyed. Once I get that "Destroyed?" properly populated, my next step is insert a PivotTable so that I summarize the data (e.g. Department 1 destroyed 50% of its items, Department 2 destroyed 2% of its items etc).

If VLOOKUP is not the right approach to this, I'm more than happy to look at other approaches. An IF function perhaps?
 
Upvote 0
Hi Mr Marcelo Branco,

Slight clarification ...In the above thread posted by Mr TomTTT, how did he bring that scroll bar in it...It would be help for for me. Is it with the help of some Add in.

Thanks in advane..Muz:)
 
Upvote 0
Hi Marcelo,

Thanks for suggesting the SUMPRODUCT but that will not cover the result I'm going for.
In my example above, I have a "Destroyed?" column on the far left. Since I have a total of around 169,000 rows in this spreadsheet, it is important for me to know which items were destroyed. Once I get that "Destroyed?" properly populated, my next step is insert a PivotTable so that I summarize the data (e.g. Department 1 destroyed 50% of its items, Department 2 destroyed 2% of its items etc).

If VLOOKUP is not the right approach to this, I'm more than happy to look at other approaches. An IF function perhaps?

Try this formula in A2 copied down

=IF(ISNUMBER(MATCH(B2,$C$2:$C$169000,0)),"Yes","No")

M.
 
Upvote 0
Hi Mr Marcelo Branco,

Slight clarification ...In the above thread posted by Mr TomTTT, how did he bring that scroll bar in it...It would be help for for me. Is it with the help of some Add in.

Thanks in advane..Muz:)

Maybe
Go Advanced
# (code tag)
Paste

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

I should have mentioned that the my columns of data include text values (e.g. ABC1000) as well as numbers (e.g. 1000). To simplify this, I have set all of these fields to be TEXT.

I changed the "ISNUMBER" portion of your formula to ISTEXT but I got confusing results. Namely, the formula is telling me "NO" when I can tell the values match just by looking at it. Does the approach you outlined work with TEXT? What am I missing?

Is there some way I could post the Excel file here? That might be easier than me attempting to describe the problem.
 
Upvote 0
@ TomTTT ...You can upload files using dropbox; then post the link here.

Muz:)
 
Upvote 0
Hi Marcelo,

I should have mentioned that the my columns of data include text values (e.g. ABC1000) as well as numbers (e.g. 1000). To simplify this, I have set all of these fields to be TEXT.

I changed the "ISNUMBER" portion of your formula to ISTEXT but I got confusing results. Namely, the formula is telling me "NO" when I can tell the values match just by looking at it. Does the approach you outlined work with TEXT? What am I missing?

Is there some way I could post the Excel file here? That might be easier than me attempting to describe the problem.


Tom,
Don't change the original formula. The formula works with text or numbers in columns B and C.
The ISNUMBER checks, for each value in column B, if MATCH returns a number ( has found the same value in column C) or an error #NA (hasn't found the value in column C), irrespective if the values are text or numbers.

M.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top