how to look up in a column and select cell below

**robknight** · 05-08-2013, 10:46 AM

Hi,

Been banging my head against a brick wall on this one for a few hours, so any help would be appreciated...

I have a single column which I need to search for a string in. Once (and only if) that string is found in any row of the column, I then need to select the value of the 3rd cell down from that one (same column, just 3 cells below the one I have found).

The match must be exact.

Any ideas please guys?! Thanks!

Rob.

**TMS** · 05-08-2013, 10:53 AM

=INDEX(A:A, MATCH("search item",A:A,0)+3)

Regards, TMS

**robknight** · 05-08-2013, 12:52 PM

Wow - thanks so much! That's almost perfect...

The only thing is - is there a way of stopping it showing "#N/A" when there is not a match?

Thanks!

**TMS** · 05-08-2013, 01:09 PM

=IFERROR(INDEX(A:A,MATCH("search item",A:A,0)+3),"")

Regards, TMS

**Jonmo1** · 05-08-2013, 01:16 PM

Try

=IFERROR(INDEX(A:A,MATCH("search item",A:A,0)+3),"")

**TMS** · 05-08-2013, 02:02 PM

@Jonmo1: thank you, well spotted. I can't quite believe I posted that. Senior moment, maybe. Though, looking at it, I think I must have pressed the wrong key at some point and re-positioned the entry point. Certainly not what I intended to type or thought I had typed.

Jonmo1's formula is the correct version: =IFERROR(INDEX(A:A,MATCH("search item",A:A,0)+3),"")

Apologies, TMS

**TMS** · 05-08-2013, 05:07 PM

You're welcome. Thanks for the rep.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.