# Vlookup help, comparing list

#### Full Spool

##### New Member
Hello,

I am having issues comparing 2 rows with vlookup. My issue is some of my numbers have a "-5" after a series of numbers. Vlookup thinks the numbers
don't exist in my reference data. I don't know how to fix my formula to ignore the "-5" and look at the numbers before so it can draw
a correct comparison.

My formula below "
=VLOOKUP(D2,Mapping!\$A\$2:\$A\$61,1,FALSE)

You can see when the -5 is in the Billing Tab and on the Mapping Tab in excel it works. However once the -5 is missing in the billing tab
it no longer correlates.

Thanks in advance for any help or direction provided.

#### AhoyNC

##### Well-known Member
Will there always be 5 numbers before the dash as in your example?

### 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.

#### Full Spool

##### New Member
Will there always be 5 numbers before the dash as in your example?
no, sometimes there is something like xx1251, or "no meter" text with no numbers at all.

#### AhoyNC

##### Well-known Member
I have not been able to figure out a reliable way to handle your second request. Hopefully someone else may be able to help.

#### Full Spool

##### New Member
Thanks for the help @AhoyNC , can you tell me what #SPILL! means and how to fix it? 17269-5 is in the mapping tab ?

#### Full Spool

##### New Member
@AhoyNC How can I rewrite the formula to compare the mapping tab values in column A to see if they match the values in column A in the billing tab? Basically the inverse of what

=FILTER('Mapping tab'!\$A\$2:\$A\$200,ISNUMBER(SEARCH(A2,'Mapping tab'!\$A\$2:\$A\$200)),"No Match")

#### AhoyNC

##### Well-known Member

Post some examples of the data in billing tab A that you want to match in column A of the mapping tab. As you are aware the issue is with the inconsistent data between the 2 tabs.

Billing Tab

Mapping Tab

#### AhoyNC

##### Well-known Member
Maybe something like this.
Just be aware that the way SEARCH works and depending on your data it could show a natch when there isn't one.

Book (4).xlsx
ABC
1BilliMeterc
211252 11252-8
311310 11310-5
411399 11399-5
511661 11661-5
611981 11981-5
712514 12514-5
812640 12640-5
912706 12706-5
1012709 12709-5
1116477 16477-0
1216480 16480-0
1316500 16500-0
1417094 17094-5
1517130 17130-0
1617135 17135-0
1717292 17292-0
1817299 17299-0
1917859 17859-0
2018215 18215-0
2116254No Match22025-55
Sheet1
Cell Formulas
RangeFormula
B2:B21B2=IF(ISNUMBER(SEARCH(\$A2,\$C\$2:\$C\$21)),"","No Match")

#### Full Spool

##### New Member
Maybe something like this.
Just be aware that the way SEARCH works and depending on your data it could show a natch when there isn't one.

Book (4).xlsx
ABC
1BilliMeterc
211252 11252-8
311310 11310-5
411399 11399-5
511661 11661-5
611981 11981-5
712514 12514-5
812640 12640-5
912706 12706-5
1012709 12709-5
1116477 16477-0
1216480 16480-0
1316500 16500-0
1417094 17094-5
1517130 17130-0
1617135 17135-0
1717292 17292-0
1817299 17299-0
1917859 17859-0
2018215 18215-0
2116254No Match22025-55
Sheet1
Cell Formulas
RangeFormula
B2:B21B2=IF(ISNUMBER(SEARCH(\$A2,\$C\$2:\$C\$21)),"","No Match")

I tried the formula but I am getting no match.

Replies
1
Views
65
Replies
2
Views
71
Replies
1
Views
169
Replies
3
Views
50
Replies
0
Views
32

1,127,167
Messages
5,623,123
Members
415,956
Latest member
Footballtend

### 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.

### Which adblocker are you using?

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

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