Find Cell Reference Containing "Text"

[vmcduarte]

Hello!

This might be a very basic question but I haven't been able to find it.

I have this:


___A______B______C
1 Blue___White___Black
2 Green__Red____Brown
3 Purple_Yellow___Pink

I want a formlula that searches on A1:C3 for the text "Red" and returns B2.

I have tried this:
MATCH("Red";A1:C3), however I return an error.

How to solve this?

Thank you!

[Ron Coderre]

With your posted data in A1:C3
and
D1: (the text to find....eg Red)

This formula returns the address of the cell containing that value:

E1: =ADDRESS(MAX(INDEX((A1:C3=D1)*ROW(A1:A3),0)),MAX(INDEX((A1:C3=D1)*
COLUMN(A1:C1),0)),4)

or...this shorter version..which must be committed with CTRL+SHIFT+ENTER,
(instead of just ENTER)

E1: =ADDRESS(MAX((A1:C3=D1)*ROW(A1:A3)),MAX((A1:C3=D1)*COLUMN(A1:C1)),4)

In the above example, the formulas return: B2

Is that something you can work with?

[vmcduarte]

That works just great, thank you!

Now, imagine that formula returned "B2", as a text string.

Cells:

X1 = 2
Y1 = -5
D7 = 999

I want to pick the "B2" and make some formula that returns the value of the cell located in "B2" plus (X1) columns to the right and (Y1) rows bellow, wich would point to cell D7.

The return of this formula would be 999, of course.

How do I make this type of "operations" with cell references?

Thank you again!

[Ron Coderre]

Perhaps something like this....

With
W1: (the formula that returns "B2")
X1: (the column offset to be used....eg 2)
Y1: (the row offset to be used, sign reversed...eg -5)
D7: (an arbitrary value...eg 999)

This formula returns the value of the cell that is Y1 rows below B2
and X1 columns to the right:

=INDEX($1:$65536,ROW(INDIRECT(W1))-Y1,COLUMN(INDIRECT(W1))+X1)

In the example, the formula returns: 999...(the value of D7)

Does that help?

[vmcduarte]

That works just great!

But I have to take baby steps in the complexity of my spreadsheet and I still have another problem to solve:

This is what I have so far:

D1: Text to Find
D1 = "test"
-----------------

D2: Function Find Cell

D2 = ADDRESS(MAX(INDEX((A1:C500=D1)*ROW(A1:C500),0)),MAX(INDEX((A1:C500=D1)*
COLUMN(A1:C500),0)),4)

D2 = D100
------------------

D3: Column Offset
D3 = 2
------------------

D4: Row Offset
D4 = 3
------------------

D5: Value of Cell (D+2Columns;100+3Rows)
D5 = INDEX($1:$65536,ROW(INDIRECT(D2))+D4,COLUMN(INDIRECT(D2))+D3)

SO FAR IT WORKS GREAT!


NEW PROBLEM:

Cells D1 - D5 are located in Sheet1
Cell Range A1:C500 are located in Sheet2


Cell Funcion in D2 works ok, but returns C100, not Sheet2!C100

Cell Function in D5 does not work.


What might be the problem?



Thank you again for all the help!

[Ron Coderre]

The iterative approach often leads to inefficient solutions.
I think you need to tell us what you *ultimately* want to do
and let us try to help you get there.

[Cutter]

Hello IanE78, and welcome to the forum.

Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.