Tried These All, XLookup, Index, & Match - No Success

[MrDB4Excel]

index and match excel at DuckDuckGo
how to use index and match to get data from multiple sheets at DuckDuckGo
As the Thread title indicates, Tried These All, XLookup, Index, & Match - No Success, I have tried many different combinations and just can’ seem to get it to work.
Sheet “MustHavePrograms2” includes column “D” which needs to, with a formula, get the corresponding value on sheet “ProgramSheetsAlphaSorted”, column labeled ‘Reinstall Status’ which simply tells me Rebuild/ Reinstall: Y = Yes, N = No, M = Maybe (this becomes obvious when the Xl2bb is viewed for these sheets).
So to recap, sheet “MustHavePrograms2” needs to get data from the column labeled ‘Reinstall Status’ in sheet “ProgramSheetsAlphaSorted” and insert it into the relevant cell in sheet “MustHavePrograms2”, column labeled ‘Reinstall Status’ per the program name.
As I said, I have tried multiple versions/ combinations of XLookup, Index, and Match, and all have only resulted in either #DATA or #N/A or even a full repeat of the formula text. To keep the Xl2bb inserts as brief as possible I haven’t included all rows (for example – sheet “ProgramSheetsAlphaSorted” has a total of 112 rows, only 34 rows are included in the Xl2bb insert).
Any help is much appreciated.

ProgramListRebuild.xlsm
	A	B	C	D
1		Name of Program	#	Reinstall Status
2	A18	Bookviser Reader	1
3	A22	Canon CanoScan LiDE220 Manual	2
4	A23	Canon Quick Menu	3
5	A24	CloneSpy 3.42 - 64 bit	4
6	A29	Dexplorer	5
7	A32	DoubleFinder	6
8	A39	Files Inspector	7
9	A56	Karen's Alarm Clock	8
10	A78	Opera Web Browser	9
11	A83	Panaustik	10
12	A86	PDF Link Editor Pro	11
13
14
15
16	$A$18	[ProgramListRebuild.xlsm]ProgramLocInfo!$A$18	A18	$A$18
17	$A$22	[ProgramListRebuild.xlsm]ProgramLocInfo!$A$22	A22	$A$22
18	$A$23	[ProgramListRebuild.xlsm]ProgramLocInfo!$A$23	A23	$A$23
19	$A$24	[ProgramListRebuild.xlsm]ProgramLocInfo!$A$24	A24	$A$24
20	$A$29	[ProgramListRebuild.xlsm]ProgramLocInfo!$A$29	A29	$A$29
21	$A$32	[ProgramListRebuild.xlsm]ProgramLocInfo!$A$32	A32	$A$32
22	$A$39	[ProgramListRebuild.xlsm]ProgramLocInfo!$A$39	A39	$A$39
23	$A$56	[ProgramListRebuild.xlsm]ProgramLocInfo!$A$56	A56	$A$56
24	$A$78	[ProgramListRebuild.xlsm]ProgramLocInfo!$A$78	A78	$A$78
25	$A$83	[ProgramListRebuild.xlsm]ProgramLocInfo!$A$83	A83	$A$83
26	$A$86	[ProgramListRebuild.xlsm]ProgramLocInfo!$A$86	A86	$A$86
MustHavePrograms2

Cell Formulas
Range	Formula
A2:A12	A2	=HYPERLINK("#"&CELL("address",INDEX(ProgramLocInfo!$A$1:$A$141,MATCH(MustHavePrograms2!B2,ProgramLocInfo!$A$1:$A$141,0))),ADDRESS(MATCH(MustHavePrograms2!B2,ProgramLocInfo!$A$1:$A$141,0),1,4,1))
A16:A26	A16	=D16
B16:B26	B16	=CELL("address",INDEX(ProgramLocInfo!$A$1:$A$141,MATCH(MustHavePrograms2!B2,ProgramLocInfo!$A$1:$A$141,0)))
C16:C26	C16	=SUBSTITUTE(SUBSTITUTE(A16,"$",""),"$","")
D16:D26	D16	=RIGHT(B16, LEN(B16)-FIND("!",B16))

ProgramListRebuild.xlsm
	A	B	C	D	E
1	Name of Program	No.	Reinstall Status	From Which Sheet	Formula Text
2	7-Zip 15.14 (x64)	1	Y	Programs_7-9	='Programs_7-9'!G3
3	Ablebits Ultimate Suite for Microsoft Excel	2	N	Programs_7-9	='Programs_7-9'!G4
4	Adobe Reader DC Ver19.021.20058	3	Y	Programs_7-9	='Programs_7-9'!G5
5	Agent Ransack x64	4	Y	Programs_7-9	='Programs_7-9'!G6
6	Aimersoft Video Converter Ultimate	1	Y	Programs_4-6	='Programs_4-6'!L3
7	Aimersoft Video Converter Ultimate	5	Y	Programs_7-9	='Programs_7-9'!G7
8	AllMyNotes Organizer	1	Y	Programs_1-3	='Programs_1-3'!G3
9	Amazon Kindle	3	Y	Programs_1-3	='Programs_1-3'!G5
10	Autodesk AutoCAD 2014	6	Y	Programs_7-9	='Programs_7-9'!G8
11	Autodesk Revit 2015	7	Y	Programs_7-9	='Programs_7-9'!G9
12	Belarc Advisor	1	Y	Programs_7-9	='Programs_7-9'!B3
13	Bitdefender Antivirus Free	1	Y	Programs_1-3	='Programs_1-3'!L3
14	Bluebeam Revu x64 2015.6	2	Y	Programs_1-3	='Programs_1-3'!G4
15	Bookviser Reader	8	Y	Programs_7-9	='Programs_7-9'!G10
16	Brave Browser	9	Y	Programs_7-9	='Programs_7-9'!G11
17	Bulk Rename Utility 3.0.0.1 (64-bit)	10	Y	Programs_7-9	='Programs_7-9'!G12
18	calibre 64bit - E-book management	1	Y	Programs_1-3	='Programs_1-3'!B3
19	Canon CanoScan LiDE220 Manual	1	Y	Programs_4-6	='Programs_4-6'!G3
20	Canon Quick Menu	2	Y	Programs_4-6	='Programs_4-6'!G4
21	CloneSpy 3.42 - 64 bit	2	Y	Programs_1-3	='Programs_1-3'!B4
22	Cool Timer	11	Y	Programs_7-9	='Programs_7-9'!G13
23	CoolUtils Outlook Viewer	3	Y	Programs_4-6	='Programs_4-6'!B5
24	CPUID CPU-Z	2	Y	Programs_7-9	='Programs_7-9'!B4
25	CutOut 5	2	Y	Programs_4-6	='Programs_4-6'!L4
26	DExplorer	3	Y	Programs_1-3	='Programs_1-3'!B5
27	Directory List & Print (Pro)	4	Y	Programs_1-3	='Programs_1-3'!B6
28	Diskeeper 12 Professional	3	Y	Programs_7-9	='Programs_7-9'!B5
29	DoubleFinder	5	Y	Programs_1-3	='Programs_1-3'!B7
30	DSynchronize	6	M	Programs_1-3	='Programs_1-3'!B8
31	Duncan's SuDoku Solver	12	N	Programs_7-9	='Programs_7-9'!G14
32	Duplicate Filter	7	Y	Programs_1-3	='Programs_1-3'!B9
33	Easy Speech2Text	3	Y	Programs_4-6	='Programs_4-6'!L5
34	FileASSASSIN	8	Y	Programs_1-3	='Programs_1-3'!B10
ProgramSheetsAlphaSorted

Cell Formulas
Range	Formula
A2:C5	A2	='Programs_7-9'!G3
D2:D34	D2	=MID(E2,3,12)
E2:E34	E2	=FORMULATEXT(A2)
A6:C6	A6	='Programs_4-6'!L3
A7:C7	A7	='Programs_7-9'!G7
A8:C8	A8	='Programs_1-3'!G3
A9:C9	A9	='Programs_1-3'!G5
A10:C11	A10	='Programs_7-9'!G8
A12:C12	A12	='Programs_7-9'!B3
A13:C13	A13	='Programs_1-3'!L3
A14:C14	A14	='Programs_1-3'!G4
A15:C17	A15	='Programs_7-9'!G10
A18:C18	A18	='Programs_1-3'!B3
A19:C20	A19	='Programs_4-6'!G3
A21:C21	A21	='Programs_1-3'!B4
A22:C22	A22	='Programs_7-9'!G13
A23:C23	A23	='Programs_4-6'!B5
A24:C24	A24	='Programs_7-9'!B4
A25:C25	A25	='Programs_4-6'!L4
A26:C27	A26	='Programs_1-3'!B5
A28:C28	A28	='Programs_7-9'!B5
A29:C30	A29	='Programs_1-3'!B7
A31:C31	A31	='Programs_7-9'!G14
A32:C32	A32	='Programs_1-3'!B9
A33:C33	A33	='Programs_4-6'!L5
A34:C34	A34	='Programs_1-3'!B10

ProgramListRebuild.xlsm
	A	B	C	D
3	A21	calibre 64bit - E-book management	1	Y
4	A24	CloneSpy 3.42 - 64 bit	2	Y
Programs_1-3

Cell Formulas
Range	Formula
A3:A4	A3	=HYPERLINK("#"&CELL("address",INDEX(ProgramLocInfo!$A$1:$A$141,MATCH('Programs_1-3'!B3,ProgramLocInfo!$A$1:$A$141,0))),ADDRESS(MATCH('Programs_1-3'!B3,ProgramLocInfo!$A$1:$A$141,0),1,4,1))

ProgramListRebuild.xlsm
	A	B	C	D
3	A59	Kernel Outlook OST Viewer	1	Y
4	A60	Kernel Outlook PST Viewer	2	Y
Programs_4-6

Cell Formulas
Range	Formula
A3:A4	A3	=HYPERLINK("#"&CELL("address",INDEX(ProgramLocInfo!$A$1:$A$141,MATCH('Programs_4-6'!B3,ProgramLocInfo!$A$1:$A$141,0))),ADDRESS(MATCH('Programs_4-6'!B3,ProgramLocInfo!$A$1:$A$141,0),1,4,1))

ProgramListRebuild.xlsm
	A	B	C	D
3	A15	Belarc Advisor	1	Y
4	A27	CPUID CPU-Z	2	Y
Programs_7-9

Cell Formulas
Range	Formula
A3:A4	A3	=HYPERLINK("#"&CELL("address",INDEX(ProgramLocInfo!$A$1:$A$141,MATCH('Programs_7-9'!B3,ProgramLocInfo!$A$1:$A$141,0))),ADDRESS(MATCH('Programs_7-9'!B3,ProgramLocInfo!$A$1:$A$141,0),1,4,1))
D3	D3	=ProgramLocInfo!G15
D4	D4	=ProgramLocInfo!G27

 

[Jeffrey Mahoney]

Xlookup worked fine for me on your data until it got to items 7-10 because they didn't exist on the other table
=XLOOKUP(MustHavePrograms2!B3,ProgramSheetsAlphaSorted!$A$2:$A$34,ProgramSheetsAlphaSorted!$C$2:$C$34)

 

[MrDB4Excel]

Many thanks, Jeffrey. I figured out why it stopped at 7-10. Fixed and now works. Here is the formula I placed in cell D3:
=XLOOKUP(MustHavePrograms2!B2,ProgramSheetsAlphaSorted!$A$2:$A$113,ProgramSheetsAlphaSorted!$C$2:$C$113)
The extent of data in ProgramSheetsAlphaSorted goes down to row 113