Would like to utilize Row Number after using Formula with =@CELL("address", INDEX(..........)))

[RapchikM]

Hello

I've used the Following formulas

Value of D7 = "12345678" is in Sheet2

in cell T7 of Sheet2 I've used below Formula
=@CELL("address",INDEX(Sheet_XLS2023!$D$15:$D$179,MATCH($D7&"*",Sheet_XLS2023!$D$15:$D$179,0)))

RESULT: '[Filename.xlsx]Sheet_XLS2023'!$D$51

ie D51 OF Sheet_XLS2023'!

using above formula
Therefore for value "12345678" i got cell address as D51 of Sheet_XLS2023'!

Then in cell U7 of Sheet 2 I type
=RIGHT(T7, LEN(T7)-31) to get as $D$51

Question 1 : How do i remove $ sign and get as D51

Question 2: How can i use Row 51 in different Formulas

For EG I would Like to use =VLOOKUP appropriately
ie to get the value of F51 from Sheet_XLS2023'!

Thanks
RapchikM

 

[Fluff]

To get the value of F51 you can use

=INDEX(Sheet_XLS2023!$F$15:$F$179,MATCH($D7&"*",Sheet_XLS2023!$D$15:$D$179,0))

 

[RapchikM]

To get the value of F51 you can use
Excel Formula:
=INDEX(Sheet_XLS2023!$F$15:$F$179,MATCH($D7&"*",Sheet_XLS2023!$D$15:$D$179,0))

Thank you sir for your prompt Reply.

How do i remove $ sign as per #1 of this thread

Thanks
RapchikM

 

[Fluff]

Why are you trying to get the address when there is no need, you can simply use the formula I posted to get the value from column F?

 

[RapchikM]

Why are you trying to get the address when there is no need, you can simply use the formula I posted to get the value from column F?

Thank You Sir,

But For Future if one wants to remove $ Sign. Thats Why I asked

RapchikM

 

[Fluff]

You can use the substitute function for that.

 

[RapchikM]

You can use the substitute function for that

Thank You

RapchikM

 

[Fluff]

You're welcome & thanks for the feedback.