Address as text back to address

Hogarth

New Member
Joined
Mar 4, 2009
Messages
5
I'm using the following formula to find contents of a cell using offset() to gather info from a cell.
=OFFSET(ADDRESS(MATCH(AO58,SKILLIST,0)+56,MATCH(AO58,SKILLIST,0)))

The problem is I don't know how to change the text address from the Address() function back to an address so OFFSET() can use it. The reference text in the Match() function could be anywhere in the list (single column).
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
For example

=OFFSET(INDEX(SKILLIST,MATCH(A8,SKILLIST,0)),1,1)
 

Hogarth

New Member
Joined
Mar 4, 2009
Messages
5
Let me show you what I mean. Here is 3 columns
col> A B C
row
5 15 test Sewing
6 16 test1 Painting
7 13 test3 Bricklaying

The Address() function locates "Painting" and finds the address C6 which I want to use in Offset(c6,0,-2,1,1) to get the value 16. I do this because "Painting" could be anywhere in column C. The range SKILLIST = C5:C7. The problem is any function I find results either in the contents of c6 or the referrence as text "C6" and offset can't use a TEXT ref. I need to know how to convert the text "C6" to a REF that can be used.
 
Last edited:

gardnertoo

Well-known Member
Joined
Jul 24, 2007
Messages
938
INDIRECT is the function you want. It converts text strings into references.

There might be a better way to go about it. If you can post a sample of what you're trying to accomplish someone here can suggest a few ideas, I'm sure.
 
Last edited:

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
The Address() function locates "Painting" and finds the address C6 which I want to use in Offset(c6,0,-2,1,1) to get the value 16. I do this because "Painting" could be anywhere in column C. The range SKILLIST = C5:C7. The problem is any function I find results either in the contents of c6 or the referrence as text "C6" and offset can't use a TEXT ref. I need to know how to convert the text "C6" to a REF that can be used.

That is exactly what my formula does.

Perhaps my use of row and column offsets of 1 threw you, if you use

=OFFSET(INDEX(SKILLIST,MATCH(A8,SKILLIST,0)),0,-2)

you will see it returns 16.
 

Hogarth

New Member
Joined
Mar 4, 2009
Messages
5
Thanks, Had try on a plain sheet and worked it out. Truly appreciate the help.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,173
Messages
5,623,191
Members
415,956
Latest member
locos

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