Fill the coloumn left side from trace the value from right side in a list

[AFZAL SOHAIL]

Dear Team,
hi,
I have a list which I shared, I want to fill the coloulm-e (EPI No.) from trace out value from right side coloum k (EPI No.)
Thanks in advance please.

Sr.No.	Name	Designation	OSIS ID	EPI No	DIVISION	Sr.No.​	Name	Designation	OSIS ID	EPI No
1​	Zulfiqar Ali​	Associate Network Technician​	700008044​		FIBER	1​	Zulfiqar Ali​	Associate Network Technician​	700008044​	HTP1655​
2​	muhammad Arif Anjum​	Associate Network Technician​	700010973​		FIBER	2​	muhammad Arif Anjum​	Associate Network Technician​	700010973​	HTP1790​
3​	Muhammad Ilyas​	Associate Network Technician​	700014513​		FIBER	3​	Muhammad Ilyas​	Associate Network Technician​	700014513​	HTP2397​
4​	Muhammad Tariq Hanif​	Associate Network Technician​	700018888​		TRANSPORT	4​	RAI M Zergham KHAN​	Associate Network Technician​	700016523​	P23120​
5​	Abdul Hannan Ali​	Associate Network Technician​	700019145​		FIBER	5​	Muhammad Tariq Hanif​	Associate Network Technician​	700018888​	PE(FMC)-401​
6​	Muhammad Daud Malik​	Associate Network Technician​	700020781​		FIBER	6​	Abdul Hannan Ali​	Associate Network Technician​	700019145​	HTP2775​
7​	Ali Sanwal​	Associate Network Technician​	700022730​		GPON	7​	Muhammad Daud Malik​	Associate Network Technician​	700020781​	P26713 25.03.22​
8​	Syed Asad Ali​	Supervisor - Field Operations​	700026141​		GPON	8​	Ali Sanwal​	Associate Network Technician​	700022730​	PE(FMC)-510​
9​	Talha Naveed​	Associate Network Technician​	700026368​		GPON	9​	Syed Asad Ali​	Associate Network Technician​	700026141​	P30362​
10​	Arslan Ahmad​	Associate Network Technician​	700026825​		TRANSPORT	10​	Talha Naveed​	Associate Network Technician​	700026368​	PE(FMC)-605​
11​	Muhammad Husnain​	Associate Network Technician​	700027324​		GPON	11​	Arslan Ahmad​	Associate Network Technician​	700026825​	PE(FMC)-609​
12​	Muhammad Azeem​	Associate Network Technician​	700028662​		GPON	12​	Muhammad Husnain​	Associate Network Technician​	700027324​	PE(FMC)-655​
13​	Muhammad Hussain Hani​	Power Technician​	700016550		FIBER	13​	Muhammad Azeem​	Associate Network Technician​	700028662​	P32039 19.04.2024
14​	Muhammad Asim​	Driver​	700007978​		FIBER	14​	Muhammad Asim​	Driver​	700007978​	HTP2692​
15​	Muhammad Shafiq​	Driver​	700007979​		FIBER	15​	Muhammad Shafiq​	Driver​	700007979​	HTP1666​

 

[sadath]

Hi
=LOOKUP(1E+50,(--RIGHT(K2,ROW(1:10))))

this will extract value at the end of the cell value/text

 

[AFZAL SOHAIL]

Hi
=LOOKUP(1E+50,(--RIGHT(K2,ROW(1:10))))

this will extract value at the end of the cell value/text

ok Thanks Sir,
I check it and then reply it..

 

[AFZAL SOHAIL]

Hello Sir Sadath:
I check it but it is not give me the complete value of k-col like this it is give me only number values

HTP1655​

HTP1790​

HTP2397​

P23120​

like this:-

1655​

1790​

2397​

23120​

 

[AFZAL SOHAIL]

Please help me....

 

[AFZAL SOHAIL]

Hi,
I requested with Mr.Excel MVP and moderator kindly help me......

 

[Cubist]

Are you looking to extract the letters or the numerical values from column K? Can you show some expected output?

 

[Cubist]

You can try this.

=LET(x,XMATCH(TRUE,ISNUMBER(--MID(K2,SEQUENCE(LEN(K2)),1))),MID(K2,x,LEN(K2)-x))

 

[AFZAL SOHAIL]

Thanks a lot Sir,
I applied this formula it is given numerical value, I need complete value like HTP1655, and other like same,also with match coloumn B name, and which not include in the coulmn B, it will be not avaliable

 

[Cubist]

As English isn't your primary language, it is difficult to understand what you're looking for. Is this what you mean?

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Sr.No.	Name	Designation	OSIS ID	EPI No	DIVISION	Sr.No.	Name	Designation	OSIS ID	EPI No
2	1	Zulfiqar Ali	Associate Network Technician	700008044	HTP1655	FIBER	1	Zulfiqar Ali	Associate Network Technician	700008044	HTP1655
3	2	muhammad Arif Anjum	Associate Network Technician	700010973	HTP1790	FIBER	2	muhammad Arif Anjum	Associate Network Technician	700010973	HTP1790
4	3	Muhammad Ilyas	Associate Network Technician	700014513	HTP2397	FIBER	3	Muhammad Ilyas	Associate Network Technician	700014513	HTP2397
5	4	Muhammad Tariq Hanif	Associate Network Technician	700018888	PE(FMC)-401	TRANSPORT	4	RAI M Zergham KHAN	Associate Network Technician	700016523	P23120
6	5	Abdul Hannan Ali	Associate Network Technician	700019145	HTP2775	FIBER	5	Muhammad Tariq Hanif	Associate Network Technician	700018888	PE(FMC)-401
7	6	Muhammad Daud Malik	Associate Network Technician	700020781	P26713 25.03.22	FIBER	6	Abdul Hannan Ali	Associate Network Technician	700019145	HTP2775
8	7	Ali Sanwal	Associate Network Technician	700022730	PE(FMC)-510	GPON	7	Muhammad Daud Malik	Associate Network Technician	700020781	P26713 25.03.22
9	8	Syed Asad Ali	Supervisor - Field Operations	700026141	P30362	GPON	8	Ali Sanwal	Associate Network Technician	700022730	PE(FMC)-510
10	9	Talha Naveed	Associate Network Technician	700026368	PE(FMC)-605	GPON	9	Syed Asad Ali	Associate Network Technician	700026141	P30362
11	10	Arslan Ahmad	Associate Network Technician	700026825	PE(FMC)-609	TRANSPORT	10	Talha Naveed	Associate Network Technician	700026368	PE(FMC)-605
12	11	Muhammad Husnain	Associate Network Technician	700027324	PE(FMC)-655	GPON	11	Arslan Ahmad	Associate Network Technician	700026825	PE(FMC)-609
13	12	Muhammad Azeem	Associate Network Technician	700028662	P32039	GPON	12	Muhammad Husnain	Associate Network Technician	700027324	PE(FMC)-655
14	13	Muhammad Hussain Hani	Power Technician	700016550	#N/A	FIBER	13	Muhammad Azeem	Associate Network Technician	700028662	P32039
15	14	Muhammad Asim	Driver	700007978	HTP2692	FIBER	14	Muhammad Asim	Driver	700007978	HTP2692
16	15	Muhammad Shafiq	Driver	700007979	HTP1666	FIBER	15	Muhammad Shafiq	Driver	700007979	HTP1666
Sheet6

Cell Formulas
Range	Formula
E2:E16	E2	=INDEX($K$2:$K$16,MATCH(B2,$H$2:$H$16,0))