Vlookup help, comparing list

Full Spool

New Member
Joined
Dec 31, 2012
Messages
22
Office Version
  1. 365
Platform
  1. Windows
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.

1611950186012.png


Thanks in advance for any help or direction provided.
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,749
Office Version
  1. 365
Platform
  1. Windows
Will there always be 5 numbers before the dash as in your example?
 

Some videos you may like

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.

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,749
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 31, 2012
Messages
22
Office Version
  1. 365
Platform
  1. Windows
Thanks for the help @AhoyNC , can you tell me what #SPILL! means and how to fix it? 17269-5 is in the mapping tab ?

1612371863141.png
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,749
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

See attached link on #SPILL.
 

Full Spool

New Member
Joined
Dec 31, 2012
Messages
22
Office Version
  1. 365
Platform
  1. Windows
@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
your original formula is doing.

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

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,749
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,749
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 31, 2012
Messages
22
Office Version
  1. 365
Platform
  1. Windows
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.

1614719626458.png
 

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top