Index Match on 2nd occurrence

[Heather_Llo]

Hi,

I am wanting to find the 2nd occurrence of "Buyer Comments:" on my sheet and have it return the cell below it. I have been using this code:

=INDEX(A:A,MATCH("*Buyer Comments:*",A:A,0)+0)

but it catches the first one.

Thank you!

 

[proficient]

Try this,

=INDEX(A:A,SMALL(IF(A:A="BUYER COMMENTS",ROW(A:A)),2), 1)

This needs to be confirmed with Ctrl-Shift-Enter

 

[Heather_Llo]

Try this,

=INDEX(A:A,SMALL(IF(A:A="BUYER COMMENTS",ROW(A:A)),2), 1)

This needs to be confirmed with Ctrl-Shift-Enter

Thank you! Unfortunately this is giving me a #NUM error.

 

[Marcelo Branco]

Try

Array formula
=INDEX(A$2:A$1000,SMALL(IF(ISNUMBER(SEARCH("BUYER COMMENTS",A$2:A$1000)),ROW(A$2:A$1000)-ROW(A$2)+1),2))
Ctrl+Shift+Enter

Adjust the range as needed but for the sake of performance avoid references to entire columns, like A:A, in array formulas.

M.

 

[Heather_Llo]

Try

Array formula
=INDEX(A$2:A$1000,SMALL(IF(ISNUMBER(SEARCH("BUYER COMMENTS",A$2:A$1000)),ROW(A$2:A$1000)-ROW(A$2)+1),2))
Ctrl+Shift+Enter

Adjust the range as needed but for the sake of performance avoid references to entire columns, like A:A, in array formulas.

M.

Thank you! That one worked.

Now I have another few questions.

1. Do array formulas not allow partial matches to content? I noticed you both put "BUYER COMMENTS" instead of my "*Buyer Comments:*".

2. This is only returning "Buyer Comments:". I am needing it to pull the cell below where "Buyer Comments:" is located, because the way I paste information in, Buyer Comments is in, for example, B2, and the actual comment is in B3. Is this formula set up to do that, or what number should I change to have it pull the cell under the one that contains "Buyer Comments:"?

3. The array formula is in F2. I have a formula in F3 that takes off the "Buyer Comments:" bit to give me the content after it. It is

=RIGHT(F2,LEN(F2)-16)

and is giving me a #VALUE ! error. Is this consequence of the array formula not pulling the cell below it?

Really appreciative of all the help.

 

[Marcelo Branco]

About your questions

1. Take a look in the help file about the SEARCH function

2. and 3.
Don't understand what you are trying to do. A small data sample along with expected result(s) would be helpful.

M.

 

[Heather_Llo]

Thanks, I'll take a look.

So basically... The way I paste the information from Amazon makes it so that the header (Buyer Comments) is in one cell, and the actual content I am needing is in another cell below it. I am trying to search on the second occurrence of "Buyer Comments" and return the cell beneath it, which is the actual comment the customer has made.

Now that I think about it, you can ignore the right formula. Take that completely out of the equation.

Does that help?

 

[Marcelo Branco]

To get the cell immediately below all you need is to add 1 to the result of SMALL(...)

=INDEX(A$2:A$1000,SMALL(IF(ISNUMBER(SEARCH("BUYER COMMENTS",A$2:A$1000)),ROW(A$2:A$1000)-ROW(A$2)+1),2)+1)
Ctrl+Shift+Enter

M.

 

[Heather_Llo]

Thank you so much! That worked perfectly. I really appreciate it. Cheers.