Display Values of RangeName List

[Dal123]

Could anyone enlighten me how one would Lookup the Address listed by the generated Rangename list in Excel 2016?
I've researched the below and I can't seem to get my head around it properly. :

• CELL
• ADDRESS
• LOOKUP
• VLOOKUP

I have the Rangename list on worksheet 3 as pictured, I want to put a new column in C e.g.:
C1, would lookup the address listed in B1.

I'm surprised there are not any direct articles relating to this. There are many in different ways but none on this exact problem. I was considering VLOOKUP in a roundabout way but the problem was that I'm searching for data over several columns.


Untitled-1.jpg

[FlameRetired]

Perhaps something like this?

=REPLACE(VLOOKUP(A1,$A$1:$B$23,2,FALSE),1,FIND("$",VLOOKUP(A1,$A$1:$B$23,2,FALSE))-1,"")

[protonLeah]

Please Login or Register  to view this content.

[FlameRetired]

Another

=ADDRESS(ROW(INDIRECT(A1)),COLUMN(INDIRECT(A1)))

[FlameRetired]

Another

=MID(VLOOKUP(A1,$A$1:$B$3,2,FALSE),FIND("$",VLOOKUP(A1,$A$1:$B$3,2,FALSE)),99)

[Dal123]

Please Login or Register  to view this content.

Thanks guys, greatly appreciate both of your input. I've found this to be the most efficient, lean formula for my needs in this instance.

Both of your input has really helped me understand the INDIRECT function now; which I am most grateful.

[FlameRetired]

Good deal. Thanks for the feedback, rep and marking your thread Solved.