To count how many appears on the destroyed list you can use
=SUMPRODUCT(COUNTIF($C$2:$C$15,$B$2:$B$15))
M.
This is a discussion on Using Vlookup to compare 2 columns of data within the Excel Questions forums, part of the Question Forums category; Hi everyone, I'm having difficulties using vlookup to compare two columns of TEXT data. My goal is find out how ...
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?
Code:
Destroyed? (i.e. Vlookup formula in this column) Eligible Serial Numbers Serial Numbers (destroyed) 362351581 362351581 362351582 362351582 362351584 362351584 362351585 362351585 362351590 362351590 362351603 362351603 362351604 362351604 362351605 362351605 362351611 362351611 362351612 362351612 362351617 99379006 362351618 99379007 362351619 142353015 362351620 142353016
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?
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:
Destroyed? (i.e. Vlookup formula in this column) Eligible Serial Numbers Serial Numbers (destroyed) 362351581 362351581 362351582 362351582 362351584 362351584 362351585 362351585 362351590 362351590 362351603 362351603 362351604 362351604 362351605 362351605 362351611 362351611 362351612 362351612 362351617 99379006 362351618 99379007 362351619 142353015 362351620 142353016
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.
@ TomTTT ...You can upload files using dropbox; then post the link here.
Muz
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.
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.
Like this thread? Share it with others