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.
 

Full Spool

New Member
Joined
Dec 31, 2012
Messages
22
Office Version
  1. 365
Platform
  1. Windows
Have you tried the formula from AhoyNC in post#4?

Yes the "openfile" pop up box displays , I hit cancel and then it says no match?

Billing MetersMapping Meters
14743No Match
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,349
Office Version
  1. 365
Platform
  1. Windows
Try removing the space from the start of the sheet in that formula.
 

Full Spool

New Member
Joined
Dec 31, 2012
Messages
22
Office Version
  1. 365
Platform
  1. Windows
Try removing the space from the start of the sheet in that formula.

I removed the spaces, help some but still not working right.

Billing MetersMapping Meters
14743No Match
1492114921-5
1492114921-5
16328No Match
16581No Match
16582No Match
17250No Match
19230No Match
19231No Match
19742No Match
19750No Match
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,349
Office Version
  1. 365
Platform
  1. Windows
Have you adjusted the ranges to suit your data?
 

Full Spool

New Member
Joined
Dec 31, 2012
Messages
22
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Have you adjusted the ranges to suit your data?
=IFERROR(INDEX('Mapping tab'!A1:A59,MATCH(A2&"*",'Mapping tab'!A1:A59,0)),"No Match")

I did but still getting several errors, I'm new at this so bare with me.

1612213655109.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,349
Office Version
  1. 365
Platform
  1. Windows
Are the meter numbers in col A of both sheets?
 

AhoyNC

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

ADVERTISEMENT

See if this works:

Billing
Book1
ABCDE
1Meter #Mapping Tab
216382-216382-2
321391-521391-5
42149521495-5
53568-5No Match
Billing
Cell Formulas
RangeFormula
E2:E5E2=FILTER('Mapping Tab'!$A$2:$A$5,ISNUMBER(SEARCH(D2,'Mapping Tab'!$A$2:$A$5)),"No Match")


Mapping Tab
Book1
A
1M1 Code
216382-2
321391-5
421495-5
521333-5
Mapping Tab
 

Full Spool

New Member
Joined
Dec 31, 2012
Messages
22
Office Version
  1. 365
Platform
  1. Windows
@AhoyNC I adjusted the formula to fit my spreadsheet =FILTER('Mapping tab'!$A$1:$A$60,ISNUMBER(SEARCH(A2,'Mapping tab'!$A$1:$A$60)),"No Match")
and it appears to be working. I will test more tomorrow, thank you all so much for the help! I will return with an update.

If you could break out and explain to me how the formula works that would be great, I'd like to increase my knowledge base.
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,749
Office Version
  1. 365
Platform
  1. Windows
The SEARCH function looks for a text string and if it finds it will return a number of the start of the sting in the cell. So in my example the SEARCH returns.
(1, #VALUE, #VALUE, #VALUE) meaning it found the numbers in the position 1 of the first cell and returned an error for the next 3 cells. When you put the ISNUMBER in front of the SEARCH it returns (TRUE, FALSE, FALSE, FALSE). The FILTER function will then return the value/text from the cells marked as TRUE.

Based on your example, I don't think this will be an issue, but the SEARCH function is looking for a sequence of numbers in your case. In the example below cells A3 and A4 are different, but the SEARCH finds the value of cell B1 in both cells and returns a number (starting position). Something to be aware of.

Book1
AB
1Search16382-2
2
316382-21
4516382-22
Sheet3
Cell Formulas
RangeFormula
B3:B4B3=SEARCH($B$1,A3)
 

Full Spool

New Member
Joined
Dec 31, 2012
Messages
22
Office Version
  1. 365
Platform
  1. Windows
The SEARCH function looks for a text string and if it finds it will return a number of the start of the sting in the cell. So in my example the SEARCH returns.
(1, #VALUE, #VALUE, #VALUE) meaning it found the numbers in the position 1 of the first cell and returned an error for the next 3 cells. When you put the ISNUMBER in front of the SEARCH it returns (TRUE, FALSE, FALSE, FALSE). The FILTER function will then return the value/text from the cells marked as TRUE.

Based on your example, I don't think this will be an issue, but the SEARCH function is looking for a sequence of numbers in your case. In the example below cells A3 and A4 are different, but the SEARCH finds the value of cell B1 in both cells and returns a number (starting position). Something to be aware of.

Book1
AB
1Search16382-2
2
316382-21
4516382-22
Sheet3
Cell Formulas
RangeFormula
B3:B4B3=SEARCH($B$1,A3)


Thank you for explaining this in greater detail, I am running into some issues that some numbers are in the mapping tab that aren't in the billing tab. Is there a way to delineate the differences and highlight them?
 

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