Using Vlookup to compare 2 columns of data

Thanks:  0
Likes:  0

# Thread: Using Vlookup to compare 2 columns of data

1. ## Using Vlookup to compare 2 columns of data

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

```

2. ## Re: Using Vlookup to compare 2 columns of data

To count how many appears on the destroyed list you can use

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

M.

3. ## Re: Using Vlookup to compare 2 columns of data

Originally Posted by Marcelo Branco
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?

4. ## Re: Using Vlookup to compare 2 columns of data

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.

5. ## Re: Using Vlookup to compare 2 columns of data

Originally Posted by TomTTT
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.

6. ## Re: Using Vlookup to compare 2 columns of data

Originally Posted by Muzama Christo
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.

Maybe
# (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

```

7. ## Re: Using Vlookup to compare 2 columns of data

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.

8. ## Re: Using Vlookup to compare 2 columns of data

@ TomTTT ...You can upload files using dropbox; then post the link here.

Muz

9. ## Re: Using Vlookup to compare 2 columns of data

Originally Posted by TomTTT
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.

10. ## Re: Using Vlookup to compare 2 columns of data

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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•