Find 2nd instance of a word in a range.

[Grumpy Grandpa]

I need a formula that will tell me the 2nd time a word appears in a
range.

Assume I have three ranges of cells in B1:G1, B2:G2, and
B3:GErespectfully, with the following names in the cells:

A B C D E F G
1 JAN Smith Jones Burch March Jones Bills
2 FEB Jones Burch March Jones Bills Smith
3 MAR Burch March Jones Bills Bills Smith


The first range, I've named "JAN", the second one "FEB" and the third
one "MAR".

In another part of the worksheet I have these names entered in a
column. Let's say in J1:J5. I've named the range J1:J5 "NAMES" and I
want to display in K1:K5 the 2nd time in range FEB that each name shows
up. What w/should my formulas in K1:K5 need to look like?

J K
1 Bills =???
2 Burch =???
3 Jones =???
4 March =???
5 Smith =???

I've started with a formula in K1, for example, that looks like this:
=INDEX(INDIRECT(A2),1,MATCH(J1,NAMES,0))

But, that only finds the first instance that the name shows up. I don't
know how to tell it to find the 2nd instance of the name in the range.

Any help would be greatly appreciated.

 

[Ron Coderre]

If FEB is defined as A2:G2, try this:

K1: =SMALL(IF((NAMES 1:1)=FEB,COLUMN(FEB)),2)
Note: Commit that array formula by holding down the [Ctrl] and [Shift] keys
when you press [Enter].

Copy down as far as needed.

Note: That formula returns the Column Number of the 2nd occurrence, not the
relative position in the range. To get that, you'd need to subtract the
number of columns that preceed the defined range.

Does that help?

***********
Regards,
Ron

 

[Domenic]

Can you provide the actual results for K1:K5?

 

[Sloth]

"I need a formula that will tell me the 2nd time a word appears in a range."
"I want to display in K1:K5 the 2nd time in range FEB that each name shows
up." - These sentences are unclear. I came up with this formula that shows
the list number the 2nd instance appears in each list.

=IF(COUNTIF($B$2:$G$2,J1)>1,MATCH(J1,$B$2:$G$2,0)+MATCH(J1,INDIRECT("R2C"&2+MATCH(J1,$B$2:$G$2,0)&":R2C7",FALSE),0),"N/A")

OR (Using your name for the range)

=IF(COUNTIF(FEB,J1)>1,MATCH(J1,FEB,0)+MATCH(J1,INDIRECT("R2C"&2+MATCH(J1,FEB,0)&":R2C7",FALSE),0),"N/A")

You didn't say what to do if there was no second occurance of a name so I
made it output "N/A". You final list will look something like this

J K
1 Bills =N/A
2 Burch =N/A
3 Jones =4
4 March =N/A
5 Smith =N/A

If this isn't what you want please clarify what you want each output to be.

 

[Grumpy Grandpa]

Well, I thought I was pretty clear. (Just goes to show you!)

What I am actually looking for is a way to find the second match of a
value in a single row range.

So, like in my sample data above, if I've given the range B1 through G1
the name "JAN", what would the formula look like to find the location
of second match of the name "Jones"?

Hope that clarifies my query.

 

[Grumpy Grandpa]

Thanks but I can't get this to work. I've entered the array formula as
you provided, but I get a #NULL! error.

 

[Domenic]

Still a little unclear as to whether you want the position (relative to
Column A or Column B) or location (cell address), but try the following
array formulas that need to be confirmed with CONTROL+SHIFT+ENTER...

For the position of the second occurrence, relative to Column A:

K1, copied down:

=SMALL(IF(Feb=J1,COLUMN(Feb)),2)

For the position of the second occurrence, relative to Column B:

K1, copied down:

=SMALL(IF(Feb=J1,COLUMN(Feb)-MIN(COLUMN(Feb))+1),2)

To prevent error values when there's no second occurrence:

=IF(COUNTIF(Feb,J1)>1,SMALL(IF(Feb=J1,COLUMN(Feb)-MIN(COLUMN(Feb))+1),2),
"NA")

For the cell address of the second occurrence:

=CELL("address",INDEX(Feb,SMALL(IF(Feb=J1,COLUMN(Feb)-MIN(COLUMN(Feb))+1)
,2)))

Hope this helps!

 

[Grumpy Grandpa]

Domenic,

I was actually looking for the position of the 2nd occurrence relative
to Column B.

That did it for me. Thanks!

GG

 

[Sloth]

I don't understand. Domenic's formula outputs the same thing as mine, and
you don't need to enter it with ctrl+shift+enter. Just out of curiosity, why
didn't you like it?