Target Cell Address

[MrDB4Excel]

=HYPERLINK("#"&CELL("address",INDEX('Bogus Sheet'!$B$2:$B$3,MATCH(D3,'Bogus Sheet'!$C$2:$C$3,0))),$H$3)
In the above formula, C2:C3 is the range in Sheet1 in which the formula needs to match the text that exists in D4 on the current Sheet (for the purpose of this formula it is another sheet besides Sheet1 called DocumentedFiles) and then hyperlink to the address in B2:B3 that is associated with the find in C2:C3.
Obviously, the workbook this bogus workbook emulates has many more rows than just the two rows shown.
The attached Xl2bb Mini Sheets help to clarify my question as follows:
I would like to have the cells in column F on Sheet DocumentedFiles include the cell address from Bogus Sheet that the hyperlink in the cells in column F jump to.
In other words, I want cell F3 to show: Folder shown on Bogus Sheet Occupies Cell B2.
I have been tinkering with formulas all day and just cannot seem to get this little bit of data to be included.
Any help toward a positive outcome will be very much appreciated.

BogusAll_PDFs_On_F-Drive .xlsx
	B	C	D	E	F	G	H
1							Helper Column
2	Sheet Name		File Name	Sheet Location This Workbook	Folder Location for This File This Workbook		Folder shown on
3	Bogus Sheet		Bogus File 01.pdf	Bogus Sheet	Folder shown on Bogus Sheet		Folder shown on Bogus Sheet
4	Bogus Sheet		Bogus File 02.pdf	Bogus Sheet	Folder shown on Bogus Sheet
DocumentedFiles

Cell Formulas
Range	Formula
E3:E4	E3	=$B$3
F3:F4	F3	=HYPERLINK("#"&CELL("address",INDEX('Bogus Sheet'!$B$2:$B$3,MATCH(D3,'Bogus Sheet'!$C$2:$C$3,0))),$H$3)
H3	H3	=$H$2&B3

BogusAll_PDFs_On_F-Drive .xlsx
	A	B	C	D	E	F	G
1	Type	Folder	File	Size	Modified	Created	Last Accessed
2	Verified	F:\BogusFolder	Bogus File 01.pdf	70	02/21/2014 --- 01:32:38	11/06/2022 --- 21:33:58	07/14/2023 --- 16:33:26
3	Verified	F:\BogusFolder	Bogus File 02.pdf	107	02/03/2012 --- 15:23:06	11/06/2022 --- 21:33:44	07/15/2023 --- 12:40:22
Bogus Sheet

 

[DanteAmor]

Hi @MrDB4Excel:

Instead of H3 you must repeat the search for the function Index:

Dante Amor
	A	B	C	D	E	F
1
2		Sheet Name		File Name	Sheet Location This Workbook	Folder Location for This File This Workbook
3		Bogus Sheet		Bogus File 01.pdf	Bogus Sheet	F:\BogusFolder
4		Bogus Sheet		Bogus File 02.pdf	Bogus Sheet	F:\BogusFolder
DocumentedFiles

Cell Formulas
Range	Formula
E3:E4	E3	=$B$3
F3:F4	F3	=HYPERLINK("#"&CELL("address",INDEX('Bogus Sheet'!$B$2:$B$3,MATCH(D3,'Bogus Sheet'!$C$2:$C$3,0))),INDEX('Bogus Sheet'!$B$2:$B$3,MATCH(D3,'Bogus Sheet'!$C$2:$C$3,0)))

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[MrDB4Excel]

Hi @MrDB4Excel:

Instead of H3 you must repeat the search for the function Index:

Dante Amor
	A	B	C	D	E	F
1
2		Sheet Name		File Name	Sheet Location This Workbook	Folder Location for This File This Workbook
3		Bogus Sheet		Bogus File 01.pdf	Bogus Sheet	F:\BogusFolder
4		Bogus Sheet		Bogus File 02.pdf	Bogus Sheet	F:\BogusFolder
DocumentedFiles

Cell Formulas
Range	Formula
E3:E4	E3	=$B$3
F3:F4	F3	=HYPERLINK("#"&CELL("address",INDEX('Bogus Sheet'!$B$2:$B$3,MATCH(D3,'Bogus Sheet'!$C$2:$C$3,0))),INDEX('Bogus Sheet'!$B$2:$B$3,MATCH(D3,'Bogus Sheet'!$C$2:$C$3,0)))

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

Works perfectly, thanks DanteAmor