Search a row for a text string and return the value in the cell underneath

[buzzers88]

Hi all,

Would anyone have the quick answer to solving the below. +1 for any help.


BLANK
BLANK
CLOCK
HANDS
BLANK
BLANK
BLANK
CLOCK
FACE


So I have a column as above, I'd like to be able to search for all values of "clock" and return the cell below eg

Clock
Hands
Face
etc

I was thinking of an index/match formula with a row +1 function but I'm not sure of the syntax of the formula.

Thanks

[sktneer]

Try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of just Enter.

Assuming your data is in the range A2:A50, then try this array formula......

In B2

Please Login or Register  to view this content.

and then copy down until you get blank cells.

[buzzers88]

Thanks sktneer!

That works perfectly for what I was looking for, but I'm not sure how it works if I needed to amend it.

Should the data be as below:


BLANK BLANK
BLANK BLANK
CLOCK BLANK
BLANK HANDS
BLANK BLANK
BLANK BLANK
BLANK BLANK
CLOCK BLANK
BLANK FACE

Would you be able to use the 'column number' part of the index function to find 'FACE' and 'HANDS' in this example as the 'ROW' part is still +1 but the column would need to be +1 also?

This would solve a problem that I forsee within my data set.

Any help would be great and worth +1 rep

Thanks

[sktneer]

Assuming your data is in the range A2:B50, then try this array formula.......

In C2

Please Login or Register  to view this content.

[buzzers88]

Thank you Sktneer. That solves all my questions.

+1 and marked solved