Address as text back to address

[Hogarth]

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).

 

[xld]

For example

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

 

[Hogarth]

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.

 

[gardnertoo]

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.

 

[xld]

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]

Thanks, Had try on a plain sheet and worked it out. Truly appreciate the help.