Convert ADDRESS() function result to literal text

ajetrumpet

Banned for being rude
Joined
Apr 12, 2008
Messages
569
Office Version
  1. 365
  2. 2016
  3. 2007
Platform
  1. Windows
Guys and Gals,

Can I use the following function result and change it to text?
Code:
=ADDRESS($C$23,2,,,"mysheet")
the result of that function, for me, is this:
Code:
mysheet!$B$10
what I want to do with that literal outcome is plug into the next function, which is this:
Code:
=MATCH($E$8,aboveResult:aboveResult)
So in the cell it looks like:
Code:
=MATCH($E$8,$F$23:$F$24)
but I just get this as a result:
Code:
#N/A

Is there a way to fix that problem? A text conversion function? I've looked through the help files but can't find what I need. thanks!
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

bryonwoods30

Board Regular
Joined
Feb 24, 2011
Messages
120
did you try just using your first formula ADDRESS($C$23,2,,,"mysheet") and putting it straight into your last formula instead of getting the result and then referencing it
 

Asator

Board Regular
Joined
Apr 5, 2010
Messages
186
it sounds like you want to be able to feed a row number to your function and have it pass that to your match statement's lookup range?

if so:
=match(lookup_value,indirect("lookupsheet!"&address($C$23,2)&":"&address($C$23+some offset,2)),0)
 

ajetrumpet

Banned for being rude
Joined
Apr 12, 2008
Messages
569
Office Version
  1. 365
  2. 2016
  3. 2007
Platform
  1. Windows
did you try just using your first formula ADDRESS($C$23,2,,,"mysheet") and putting it straight into your last formula instead of getting the result and then referencing it

yes I did, no luck.

I will try Indirect() and get back to you guys. thanks!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,303
Messages
5,836,526
Members
430,437
Latest member
Emilycr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top