Look up text and return cell address which can be used for calculations

Hi,
Any help with this problem would be greatly appreciated.

Suppose I have a worksheet that looks like the following:

A B C
1 Cat

2 9 4

3 Dog

4 2 7

I need to first have a cell (I'll call it the final cell to avoid confusion) that finds the specific text I am looking for. For example, say I want to find Dog which is in A3. The text I am looking for will always be in column A, but it may not always be in the same row. For instance, I import data from a website but depending on the time of day, Cat may be in row 3 instead of 1 and vice versa.

After the "final cell" has found the cell address containing the specific text, I need it to move down 1 row and move right right 1 column. In the end, I need the "final cell" to display this value. In this example, the "final cell" value should be 2.

Thanks for your knowledge:-)

Redseal

Does this work?

=OFFSET(INDIRECT("A" & MATCH("dog",A:A,0)),1,1)

Try

=INDEX(B2:B11,MATCH("Dog",A1:A10,0))

Both suggestions work...thanks for the help, it is much appreciated.

Since that solution seemed pretty easy (at least for the pros), is there an easy way to expand the functionality of the function? What I mean is, suppose dog appears twice in column A, but the values for that instance are different. Is there a way to find both instances of dog and their corresponding value in one row down and one column to the right?

If not, that's OK, but it would be great if there was a way. Thanks again.

You can do it but with formulas it's a little more complex. If you use this formula in cell D1

=IF(COUNTIF(A$1:A$10,"Dog")<ROWS(D$1:D1),"",INDEX(B$2:B$11,SMALL(IF(A$1:A$10="Dog",ROW(A$1:A$10)-ROW(A$1)+1),ROWS(D$1:D1))))

confirmed with CTRL+SHIFT+ENTER.

It will give your result for all matches

Note: to confirm with CTRL+SHIFT+ENTER you need to hold down CTRL and SHIFT keys and press ENTER so that curly braces like { and } appear around the formula in the formula bar

You can probably do it with formulae but that's beyond me. This code puts the values into C1 down:

Please Login or Register  to view this content.

Both solutions work. Thanks for the help!