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.

 

[Saba Sabaratnam]

Hi

I have the following layout of workbook.

Date in the Mapping Tab is

Enter the following array formula by pressing Control + Shift + Enter keys together in Cell B1 in Billing Tab

=VLOOKUP(A1,MID('Mapping Tab'!$A$1:$A$9,1,FIND("-5",'Mapping Tab'!A1,1)-1)*1,1,FALSE)

Kind regards

Saba

 

[Full Spool]

Looks like that is doing what I need, I just need to somehow format it to fit my spreadsheet. So I don't get errors.
Thank you !

Billing"

Mapping Tab"

 

[AhoyNC]

Maybe something like this.
It also helps to let us know what version of Excel you are using.

Billing

Book1
	A	B	C	D	E
1				Meter #	Mapping Tab
2				16382-2	16382-2
3				21391-5	21391-5
4				21495	21495-5
5				3568-5	No Match
Billing

Cell Formulas
Range	Formula
E2:E5	E2	=IFERROR(INDEX(' Mapping Tab'!$A$2:$A$5,MATCH(D2&"*",' Mapping Tab'!$A$2:$A$5,0)),"No Match")

Mapping Tab

Book1
	A
1	M1 Code
2	16382-2
3	21391-5
4	21495-5
5	21333-5
Mapping Tab

 

[Full Spool]

I am on the latest 365 version of excel.

 

[Fluff]

I am on the latest 365 version of excel.

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Full Spool]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Updated, thank you!

 

[Saba Sabaratnam]

Hi Full Spool,

As you have 365 Excel, I used XLOOUP to solve your problem.

Layout of my workbook contains the following tabs. :

Mapping tab has the following data

Billing tab has the following layout.

Enter the following formula in B1 and copy it down.

=XLOOKUP(A1,(MID('Mapping tab'!A1,1,FIND("-",'Mapping tab'!A1,1)-1)*1),(MID('Mapping tab'!A1,1,FIND("-",'Mapping tab'!A1,1)-1)*1))

Kind regards

Saba

 

[Full Spool]

This is from the Billing tab

Billing Meters	Mapping Meters
14743	14743​
14921	#N/A​
14921	#N/A​
16328	#N/A​
16581	#N/A​
16582	#N/A​
17250	#N/A​
19230	#N/A​
19231	#N/A​
19742	#N/A​

Mapping Tab

14743-5
14744-5
14745-5
14920-5
14921-5
15723-5
15725-5
16328-2
16581-5
16582-5
17250-5
19230-5
19231-5
19742-5 I keep getting N/A errors or Value errors Anyone have a suggestion? I'm outside my wheel house with excel formulas. Thank you =XLOOKUP(A2,(MID('Mapping tab'!A1,1,FIND("-",'Mapping tab'!A1,1)-1)*1),(MID('Mapping tab'!A1,1,FIND("-",'Mapping tab'!A1,1)-1)*1))

 

[Fluff]

Have you tried the formula from AhoyNC in post#4?