Vlookup and the Right Function

[scott.wilson]

I've search the forums and can't find an answer to my particular problem or can't get what I've found to work.

I'm trying the compare the data in two columns and display in a third the results if there is a match and "0" if not so I can get a count.

Vcenter is the defined name for Column B.

The first formula I used (Column C) worked for the first set of data:
=IF(ISNA(VLOOKUP(A6,VCenter,1,FALSE)),"0",VLOOKUP(A6,VCenter,1,FALSE))

However, I'm getting a lot of "0's" where they don't match. Basic review indicates there are slight differences in the naming convention (namely characters 6&7) and therefore I need to further identify the results in yet another column to find out how many are like those in bold below.

I've tried two separate formula's in Column D and as you can see, I get #N/A as a result. The formula I'm using in Column D is:
=RIGHT(VLOOKUP(A190,VCenter,1,FALSE),6) which resulted in what you see
=VLOOKUP(RIGHT(A6,6),RIGHT(VCenter,6),1,FALSE) which results in #N/A.

Since the naming conventions are different in some cases between Columns A & B, I'm trying to find out how many match the last six characters by using the right function as part of the the VLookup.

Can you tell me what I'm doing wrong?

Thanks


This is an actual set of the data I'm evaluating:
A B C D
VCHC002VL003219vchc002vl003220vchc002vl003219003219VCHC002VL003220VCMS300VD005720vchc002vl003220003220VHHI012VL002682vecc002vl0032260#N/AVHHI012VL002683VGDIDB0VD0055330#N/AVISR022VL006393vipfjb2vl0047360#N/AVISR032VL006394vipfor2vl0047370#N/AVISR042VL006395visr012vl0037460#N/AVISR062VL006397visr012vl0061960#N/AVISR072VL006398visr012vl0061970#N/AVISR082VL006399visr012vl0061980#N/AVISR092VL006400visr012vl0061990#N/AVISR112VL006402visr012vl0062010#N/AVISR132VL006404visr012vl0062030#N/AVISR142VL006405VISR012VL0063920#N/AVISR162VL006407VISR012VL0063940#N/AVISR22VL006393VISR052VL0063970#N/Avl2sdetwebair00VISR152VL0064060#N/A

 

[alow]

I can tell you to for starters that you can't use the RIGHT function in the Table Array part of the VLOOKUP. You would need a separate column with the formula =RIGHT(A6,6) to get the value that you want to use in the table array. Also, if you're just looking to see if there is a match, you could use =IF(ISERROR(MATCH()),0,A6) (I'll dig in when I have a chance to give you specifics if this doesn't do it for you.

 

[scott.wilson]

Look at the attached Word document for the data set I'm working with.

 

[scott.wilson]

Looking at the data;

Column A: VISR032VL006394
Coumn B: VISR012VL006394

There appear to many of these. So if I understand correctly, I need to add a column to display the last six characters of the data in Column B and then run the Vlookup as I did in Column C?

 

[alow]

Correct. You would need column B to have =RIGHT(A6,6) to pull out the number part of it. Then you would have =IF(ISNA(VLOOKUP(B6,Table,1,0),0,A6). I think this will give you what you need. You also need to make sure that you have the same 6 digit format for the numbering in the table, if I understand you correctly. Also, I don't open attachments in forums. It's not that I don't trust you, I don't trust anyone. If this doesn't get it for you, let me know the rows/columns of the table you're matching the data from and I can get a little more specific.

 

[scott.wilson]

Thanks alow. Good information. I appreciate the help.