Formula to match 5-digit numbers in Column A and M, add name/text in Column I to Column B

[mr_ITtoyou]

How would I do a formula that would match the numbers in column A and H, when they match, then add the name/text from column I to column B, if any text?
I have over a 1,500 numbers on the list and I'm running the numbers from different phone system and pulling the numbers and names.

Book4
	A	B	C	D	E	F	G	H	I
1	Number	Name						Number from system	Names in system
2	24700							24700
3	24701							24701	PAGNET, PAGING #1
4	24702							24702
5	24703							24703	2, SOUTH MAIN NURSE STA.
6	24704							24704	Phone Rm Test Phone
7	24705							24705	LABORTORY MENU 24705
8	24706							24706	DIETARY, FAX
9	24707							24707	FINANCE
10	24708							24708	NEURO, DIAGNOSIS FAX
11	24709							24709	LAB 1100 Building Fax
12	24710							24710
13	24711							24711	MED. REC. BSMNT FAX
14	24712							24713
15	24713							24714	KARL, VAN ALLEN
16	24714							24715
17	24715							24716	SYNERGY
18	24716							24717	NICU, FRONT OFFICE
19	24717							24718	ED Lobby Frnt. Dsk. fax
20	24718							24719
21	24719							24720	Vineyard TW. Bed Contrl Fax
22	24720							24721	HR MAIN MENU
23	24721							24722
24	24722							24723
25	24723							24724	ICU, FAX
26	24724							24725	OPEN, HEART SURGERY
27	24725							24726
28	24726							24727	SURGERY, FAX
29	24727							24728	MATERNITY, FAX
30	24728							24729
31	24729							24730	BUS., OFFICE FAX ADMITTING
32	24730							24731	ED MAIN FAX
33	24731							24733	ACU, FAX
34	24732							24734
35	24733							24735	CSDU, FAX
36	24734
37	24735
38
Sheet1

 

[DanteAmor]

Try:

varios 17nov2023.xlsm
	A	B	C	D	E	F	G	H	I
1	Number	Name						Number from system	Names in system
2	24700							24700
3	24701	PAGNET, PAGING #1						24701	PAGNET, PAGING #1
4	24702							24702
5	24703	2, SOUTH MAIN NURSE STA.						24703	2, SOUTH MAIN NURSE STA.
6	24704	Phone Rm Test Phone						24704	Phone Rm Test Phone
7	24705	LABORTORY MENU 24705						24705	LABORTORY MENU 24705
8	24706	DIETARY, FAX						24706	DIETARY, FAX
9	24707	FINANCE						24707	FINANCE
10	24708	NEURO, DIAGNOSIS FAX						24708	NEURO, DIAGNOSIS FAX
11	24709	LAB 1100 Building Fax						24709	LAB 1100 Building Fax
12	24710							24710
13	24711	MED. REC. BSMNT FAX						24711	MED. REC. BSMNT FAX
14	24712							24713
15	24713							24714	KARL, VAN ALLEN
16	24714	KARL, VAN ALLEN						24715
17	24715							24716	SYNERGY
18	24716	SYNERGY						24717	NICU, FRONT OFFICE
19	24717	NICU, FRONT OFFICE						24718	ED Lobby Frnt. Dsk. fax
20	24718	ED Lobby Frnt. Dsk. fax						24719
21	24719							24720	Vineyard TW. Bed Contrl Fax
22	24720	Vineyard TW. Bed Contrl Fax						24721	HR MAIN MENU
23	24721	HR MAIN MENU						24722
24	24722							24723
25	24723							24724	ICU, FAX
26	24724	ICU, FAX						24725	OPEN, HEART SURGERY
27	24725	OPEN, HEART SURGERY						24726
28	24726							24727	SURGERY, FAX
29	24727	SURGERY, FAX						24728	MATERNITY, FAX
30	24728	MATERNITY, FAX						24729
31	24729							24730	BUS., OFFICE FAX ADMITTING
32	24730	BUS., OFFICE FAX ADMITTING						24731	ED MAIN FAX
33	24731	ED MAIN FAX						24733	ACU, FAX
34	24732							24734
35	24733	ACU, FAX						24735	CSDU, FAX
36	24734
37	24735	CSDU, FAX
Hoja4

Cell Formulas
Range	Formula
B2:B37	B2	=IFERROR(IF(VLOOKUP(A2,H:I,2,0)="","",VLOOKUP(A2,H:I,2,0)),"")

 

[Peter_SSs]

.. or possibly this shortened version.

23 11 18.xlsm
	A	B						H	I
1	Number	Name						Number from system	Names in system
2	24700							24700
3	24701	PAGNET, PAGING #1						24701	PAGNET, PAGING #1
4	24702							24702
5	24703	2, SOUTH MAIN NURSE STA.						24703	2, SOUTH MAIN NURSE STA.
6	24704	Phone Rm Test Phone						24704	Phone Rm Test Phone
7	24705	LABORTORY MENU 24705						24705	LABORTORY MENU 24705
8	24706	DIETARY, FAX						24706	DIETARY, FAX
9	24707	FINANCE						24707	FINANCE
10	24708	NEURO, DIAGNOSIS FAX						24708	NEURO, DIAGNOSIS FAX
11	24709	LAB 1100 Building Fax						24709	LAB 1100 Building Fax
12	24710							24710
13	24711	MED. REC. BSMNT FAX						24711	MED. REC. BSMNT FAX
14	24712							24713
15	24713							24714	KARL, VAN ALLEN
16	24714	KARL, VAN ALLEN						24715
17	24715							24716	SYNERGY
18	24716	SYNERGY						24717	NICU, FRONT OFFICE
19	24717	NICU, FRONT OFFICE						24718	ED Lobby Frnt. Dsk. fax
20	24718	ED Lobby Frnt. Dsk. fax						24719
21	24719							24720	Vineyard TW. Bed Contrl Fax
22	24720	Vineyard TW. Bed Contrl Fax						24721	HR MAIN MENU
23	24721	HR MAIN MENU						24722
24	24722							24723
25	24723							24724	ICU, FAX
26	24724	ICU, FAX						24725	OPEN, HEART SURGERY
27	24725	OPEN, HEART SURGERY						24726
28	24726							24727	SURGERY, FAX
29	24727	SURGERY, FAX						24728	MATERNITY, FAX
30	24728	MATERNITY, FAX						24729
31	24729							24730	BUS., OFFICE FAX ADMITTING
32	24730	BUS., OFFICE FAX ADMITTING						24731	ED MAIN FAX
33	24731	ED MAIN FAX						24733	ACU, FAX
34	24732							24734
35	24733	ACU, FAX						24735	CSDU, FAX
36	24734
37	24735	CSDU, FAX
Lookup

Cell Formulas
Range	Formula
B2:B37	B2	=IFNA(VLOOKUP(A2,H:I,2,0)&"","")

 

[mr_ITtoyou]

Thank you both Dente Amor and Peter_SSs, I appreciate your help on this. I'm not sure why the text is not showing up in column B. I checked the cell format, and made sure that the font color was not White. I placed the formula in B2, copied it down the column B, B3-=IFNA(VLOOKUP(A3,H:I,2,0)&"","") that has text in I3 put not showing in B3. What am I missing?

Book4
	A	B	C	D	E	F	G	H	I
1	Number	Name						Number from system	Names in system
2	24700							24700
3	24701							24701	PAGNET, PAGING #1
4	24702							24702
5	24703							24703	2, SOUTH MAIN NURSE STA.
6	24704							24704	Phone Rm Test Phone
7	24705							24705	LABORTORY MENU 24705
8	24706							24706	DIETARY, FAX
9	24707							24707	FINANCE
10	24708							24708	NEURO, DIAGNOSIS FAX
11	24709							24709	LAB 1100 Building Fax
12	24710							24710
13	24711							24711	MED. REC. BSMNT FAX
14	24712							24713
15	24713							24714	KARL, VAN ALLEN
16	24714							24715
17	24715							24716	SYNERGY
18	24716							24717	NICU, FRONT OFFICE
19	24717							24718	ED Lobby Frnt. Dsk. fax
20	24718							24719
21	24719							24720	Vineyard TW. Bed Contrl Fax
22	24720							24721	HR MAIN MENU
23	24721							24722
24	24722							24723
25	24723							24724	ICU, FAX
26	24724							24725	OPEN, HEART SURGERY
27	24725							24726
Sheet1

Cell Formulas
Range	Formula
B2:B27	B2	=IFNA(VLOOKUP(A2,H:I,2,0)&"","")

 

[Peter_SSs]

I'm not sure why the text is not showing up in column B.

One possibility would be that column A is text and column H is numerical or vice-versa. What do these return if placed in vacant cells?
=ISNUMBER(A2)
=ISNUMBER(H2)

 

[mr_ITtoyou]

One possibility would be that column A is text and column H is numerical or vice-versa. What do these return if placed in vacant cells?
=ISNUMBER(A2)
=ISNUMBER(H2)

Well Column A and Column H were formated different, A was General and H was Text, so I made them both General. The text still did not show so I opened a new Excel sheet and it worked in the new Excel sheet.
I added these in the sheet that did not show the text and I got
=ISNUMBER(A2) TRUE
=ISNUMBER(H2) FALSE
I'll start on a new Excel sheet, thanks for your help.

 

[Peter_SSs]

I'll start on a new Excel sheet,

No need to do that. With the original sheet, just change the formula in B2 to

=IFNA(VLOOKUP(A2&"",H:I,2,0)&"","")

 

[mr_ITtoyou]

No need to do that. With the original sheet, just change the formula in B2 to

=IFNA(VLOOKUP(A2&"",H:I,2,0)&"","")

That worked great. Thanks again. Have a great weekend.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.