Vlookup help, comparing list

[Full Spool]

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.

 

[jtakw]

Hi,

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
	A	B
1	Search	16382-2
2
3	16382-2	1
4	516382-2	2
Sheet3

Cell Formulas
Range	Formula
B3:B4	B3	=SEARCH($B$1,A3)

Add unique character or space to avoid this problem.

Book3.xlsx
	A	B
1	Search	16382-2
2
3	16382-2	1
4	516382-2	#VALUE!
Sheet814

Cell Formulas
Range	Formula
B3:B4	B3	=SEARCH(" "&B$1&" "," "&A3&" ")