Vlookup help, comparing list

Full Spool

New Member
Joined
Dec 31, 2012
Messages
25
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.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Try removing the space from the start of the sheet in that formula.
 
Upvote 0
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
 
Upvote 0
Have you adjusted the ranges to suit your data?
 
Upvote 0
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
 
Upvote 0
Are the meter numbers in col A of both sheets?
 
Upvote 0
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
 
Upvote 0
@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.
 
Upvote 0
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)
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

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
Back
Top