Match 2 Worksheet and put exact number if it matches in the second sheet.

[nidhinair22]

Hi...

I have a query. I have an excell sheet with 2000 rows of different vehicle mumbers. I each row there it will be 1 vehilce number, which i'm having in my second sheet. I want to extract that vehicle number and to put it in a seperate column. Please help me to match with a formula.

Invh Annotation	VEH #
DELIVERY AT RUMAIZ, CONT 96562814. CASH TRF IN BM RO.. 750.. DT 31/5/2023..VEH NO. 1154 DRIVER- ABU NASSER
DELIVERY AT LISUQ, CONT 93582921.. CASH TRF IN BM RO..3500.. DT 31/5/23.-VEH NO. 6924DS Tarseem
DELIVERY AT SUMAIL.CONTACT-IQBAL-94023163--- 92660283--VEH NO. 6924DS DRIVER- TARSEEM
DELIVERY AT SUMAIL, CONTACT NO - 99102175-- 93090671-VEH NO. 6924DS DRIVER- TARSEEM
DELIVERY TO SEEB CONT: 95441442--VEH NO. 4886 MK- BALRAJ
DELIVERY AT SUMAIL.CONTACT-91830183--VEH NIO. 6924DS DRIVER- TARSEEM
DELIVERY AT SUMAIL, CONTACT NO - 99102175-- 93090671-VEH NO. 6924DS DRIVER- TARSEEM
DELIVERY AT MISFAH, CONTACT NO - 77173212--VEH NO. 6894DS DRIVER- BALRAJ
DELIVERY AT MISFA CONT:-CONT:-92765187..F- 94610485- CASH TRF- DT- 31/05/2023--VEH NO.6894 DRIVER- BALRAJ
DELIEVRY AT BOUSHAR MONA , CONTACT NO - 99351760--CASH TRF- DT---6894DS DRIVER- BALRAJ
DELIEVRY AT BOUSHAR MONA , CONTACT NO - 99351760--CASH TRF- DT---6894DS DRIVER- BALRAJ
EX GHALA YARD..DRIVER-MUHAMMAD ABDUL NISAR- VEH NO. 5610YW.
DELIVERY AT WUDAM..CONTACT DINTO-91489249..VEH NO...6943MK DRIVER SATPAL SINGH..LOADING FROM MUSANNA YARD
MATERIAL DIRECTLY SUPPLIED TO METO UNDER MD-50565 DO NO-SB23530-017 VEH NO - 7324HW DRV- SHAHZAD--MANUAL DO -50565
DELIVERY AT BIDIYA CONT-94133339 DRV SAMI ULLAH VEH 2533/YR
DELIVERY AT W.B.K CONT-96188300 F-78061971 DRV SAMI ULLAH VEH 2533/YR
DELIVERY AL-BEDIYA -99428587,97002285 F-92678174, DRV - SAMI ULLAH VEH 2533/YR
DELIVERY AL-BEDIYA -99428587,97002285 F-92678174, DRV - SAMI ULLAH VEH 2533/YR
DELIVERY AT HAIMA CONT-93667755 F-71228389 DRV SAMI ULLAH VEH 2533/YR
DELIVERY AT JARDA CONT-98427013 F99640586 DRV SAMI ULLAH VEH 2533/YR

VEH #

2532YR

1156YW

5303WB

6943MK

8209RK

6942MK

3914DK

4475DS

9161YR

9574RK

1154YW

2542HA

2891AK

8556ML

2533YR

2662MD

5686DS

4886MK

5001MK

6778MK

6894DS

6924DS

3135WB

4071RK

 

[Skybluekid]

Try the below:

Book1.xlsm
	B	C	D	E
2	Invh Annotation	VEH #		VEH #
3	DELIVERY AT RUMAIZ, CONT 96562814. CASH TRF IN BM RO.. 750.. DT 31/5/2023..VEH NO. 1154 DRIVER- ABU NASSER			2532YR
4	DELIVERY AT LISUQ, CONT 93582921.. CASH TRF IN BM RO..3500.. DT 31/5/23.-VEH NO. 6924DS Tarseem	6924DS		1156YW
5	DELIVERY AT SUMAIL.CONTACT-IQBAL-94023163--- 92660283--VEH NO. 6924DS DRIVER- TARSEEM	6924DS		5303WB
6	DELIVERY AT SUMAIL, CONTACT NO - 99102175-- 93090671-VEH NO. 6924DS DRIVER- TARSEEM	6924DS		6943MK
7	DELIVERY TO SEEB CONT: 95441442--VEH NO. 4886 MK- BALRAJ	4886 MK		8209RK
8	DELIVERY AT SUMAIL.CONTACT-91830183--VEH NIO. 6924DS DRIVER- TARSEEM	6924DS		6942MK
9	DELIVERY AT SUMAIL, CONTACT NO - 99102175-- 93090671-VEH NO. 6924DS DRIVER- TARSEEM	6924DS		3914DK
10	DELIVERY AT MISFAH, CONTACT NO - 77173212--VEH NO. 6894DS DRIVER- BALRAJ	6894DS		4475DS
11	DELIVERY AT MISFA CONT:-CONT:-92765187..F- 94610485- CASH TRF- DT- 31/05/2023--VEH NO.6894 DRIVER- BALRAJ			9161YR
12	DELIEVRY AT BOUSHAR MONA , CONTACT NO - 99351760--CASH TRF- DT---6894DS DRIVER- BALRAJ	6894DS		9574RK
13	DELIEVRY AT BOUSHAR MONA , CONTACT NO - 99351760--CASH TRF- DT---6894DS DRIVER- BALRAJ	6894DS		1154YW
14	EX GHALA YARD..DRIVER-MUHAMMAD ABDUL NISAR- VEH NO. 5610YW.			2542HA
15	DELIVERY AT WUDAM..CONTACT DINTO-91489249..VEH NO...6943MK DRIVER SATPAL SINGH..LOADING FROM MUSANNA YARD	6943MK		2891AK
16	MATERIAL DIRECTLY SUPPLIED TO METO UNDER MD-50565 DO NO-SB23530-017 VEH NO - 7324HW DRV- SHAHZAD--MANUAL DO -50565			8556ML
17	DELIVERY AT BIDIYA CONT-94133339 DRV SAMI ULLAH VEH 2533/YR	2533/YR		2533YR
18	DELIVERY AT W.B.K CONT-96188300 F-78061971 DRV SAMI ULLAH VEH 2533/YR	2533/YR		2662MD
19	DELIVERY AL-BEDIYA -99428587,97002285 F-92678174, DRV - SAMI ULLAH VEH 2533/YR	2533/YR		5686DS
20	DELIVERY AL-BEDIYA -99428587,97002285 F-92678174, DRV - SAMI ULLAH VEH 2533/YR	2533/YR		4886 MK
21	DELIVERY AT HAIMA CONT-93667755 F-71228389 DRV SAMI ULLAH VEH 2533/YR	2533/YR		5001MK
22	DELIVERY AT JARDA CONT-98427013 F99640586 DRV SAMI ULLAH VEH 2533/YR	2533/YR		6778MK
23				6894DS
24				6924DS
25				3135WB
26				4071RK
27				2533/YR
Sheet8

Cell Formulas
Range	Formula
C3:C22	C3	=IF(ISNUMBER(LOOKUP(2^15,SEARCH($E$3:$E$27,B3))),MID(B3,LOOKUP(2^15,SEARCH($E$3:$E$27,B3)),7),"")

You will ecounter issues as the Vehicle table needs to have the same format as the extraction. Eg you have 2533YR in the vehicle table and 2533/YR in the extraction. A possibility is to have both 2533YR and 2533/YR in the vehicle table. In order to get the formula to work, I changed 2533YR to 2533/YR.

 

[Skybluekid]

Here is an alternative:

Book1.xlsm
	A	B	C	D	E	F
2		Invh Annotation	VEH #		VEH #	Helper
3		DELIVERY AT RUMAIZ, CONT 96562814. CASH TRF IN BM RO.. 750.. DT 31/5/2023..VEH NO. 1154 DRIVER- ABU NASSER	1154YW		2532YR	2532
4		DELIVERY AT LISUQ, CONT 93582921.. CASH TRF IN BM RO..3500.. DT 31/5/23.-VEH NO. 6924DS Tarseem	6924DS		1156YW	1156
5		DELIVERY AT SUMAIL.CONTACT-IQBAL-94023163--- 92660283--VEH NO. 6924DS DRIVER- TARSEEM	6924DS		5303WB	5303
6		DELIVERY AT SUMAIL, CONTACT NO - 99102175-- 93090671-VEH NO. 6924DS DRIVER- TARSEEM	6924DS		6943MK	6943
7		DELIVERY TO SEEB CONT: 95441442--VEH NO. 4886 MK- BALRAJ	4886MK		8209RK	8209
8		DELIVERY AT SUMAIL.CONTACT-91830183--VEH NIO. 6924DS DRIVER- TARSEEM	6924DS		6942MK	6942
9		DELIVERY AT SUMAIL, CONTACT NO - 99102175-- 93090671-VEH NO. 6924DS DRIVER- TARSEEM	6924DS		3914DK	3914
10		DELIVERY AT MISFAH, CONTACT NO - 77173212--VEH NO. 6894DS DRIVER- BALRAJ	6894DS		4475DS	4475
11		DELIVERY AT MISFA CONT:-CONT:-92765187..F- 94610485- CASH TRF- DT- 31/05/2023--VEH NO.6894 DRIVER- BALRAJ	6894DS		9161YR	9161
12		DELIEVRY AT BOUSHAR MONA , CONTACT NO - 99351760--CASH TRF- DT---6894DS DRIVER- BALRAJ	6894DS		9574RK	9574
13		DELIEVRY AT BOUSHAR MONA , CONTACT NO - 99351760--CASH TRF- DT---6894DS DRIVER- BALRAJ	6894DS		1154YW	1154
14		EX GHALA YARD..DRIVER-MUHAMMAD ABDUL NISAR- VEH NO. 5610YW.			2542HA	2542
15		DELIVERY AT WUDAM..CONTACT DINTO-91489249..VEH NO...6943MK DRIVER SATPAL SINGH..LOADING FROM MUSANNA YARD	6943MK		2891AK	2891
16		MATERIAL DIRECTLY SUPPLIED TO METO UNDER MD-50565 DO NO-SB23530-017 VEH NO - 7324HW DRV- SHAHZAD--MANUAL DO -50565			8556ML	8556
17		DELIVERY AT BIDIYA CONT-94133339 DRV SAMI ULLAH VEH 2533/YR	2533YR		2533YR	2533
18		DELIVERY AT W.B.K CONT-96188300 F-78061971 DRV SAMI ULLAH VEH 2533/YR	2533YR		2662MD	2662
19		DELIVERY AL-BEDIYA -99428587,97002285 F-92678174, DRV - SAMI ULLAH VEH 2533/YR	2533YR		5686DS	5686
20		DELIVERY AL-BEDIYA -99428587,97002285 F-92678174, DRV - SAMI ULLAH VEH 2533/YR	2533YR		4886MK	4886
21		DELIVERY AT HAIMA CONT-93667755 F-71228389 DRV SAMI ULLAH VEH 2533/YR	2533YR		5001MK	5001
22		DELIVERY AT JARDA CONT-98427013 F99640586 DRV SAMI ULLAH VEH 2533/YR	2533YR		6778MK	6778
23					6894DS	6894
24					6924DS	6924
25					3135WB	3135
26					4071RK	4071
Sheet8

Cell Formulas
Range	Formula
C3:C22	C3	=IF(ISNUMBER(LOOKUP(2^15,SEARCH($F$3:$F$26,B3))),INDEX($E$3:$E$26,MATCH(MID(B3,LOOKUP(2^15,SEARCH($F$3:$F$26,B3)),4),$F$3:$F$26,0)),"")
F3:F26	F3	=LEFT(E3,4)

What I have had to do is create a table of the vehicles and add a helper column.

What the formula does is looks to see if the registration is in the text. If so, then it takes the first 4 digits and looks in the helper column and returns the regstration. If the registration is not in the list it will return a blank

An advantage of a table is that if you need to add a registration, just add it to the bottom of the list.