Finding nth Duplicate In A Horizontal List

[Jesbass]

Hi there. This seems like it should be really easy, but I've been wracking my brain over it for the last few hours.

I have a spreadsheet I'm using that has a number of locations in one row, and I want to retrieve the data from the appropriate column based on the location name. For the most part, that's simple enough, (HLOOKUP does the trick), but for locations that have duplicates, I'm stumped.

I've created some dummy data and can find the nth instance of a duplicate when the list is vertical (Cells A1:B6):
[TABLE="width: 500"]
<tbody>[TR]
[TD]Philadelphia[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Long Beach[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Honolulu[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Miami[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Philadelphia[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Long Beach[/TD]
[TD]F[/TD]
[/TR]
</tbody>[/TABLE]

The following formula searches for the 2nd instance of Philadelphia, and correctly returns "E" as a result:
{=INDEX($B$1:$B$6,SMALL(IF($A$1:$A$6="Philadelphia",ROW($A$1:$A$6),""),2),1)}

However, my attempts to translate this to a horizontal list (and thus use that as a basis to work with the spreadsheet in question) have failed (Cells A1:F2):
[TABLE="width: 500"]
<tbody>[TR]
[TD]Philadelphia[/TD]
[TD]Long Beach[/TD]
[TD]Honolulu[/TD]
[TD]Miami[/TD]
[TD]Philadelphia[/TD]
[TD]Long Beach[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
</tbody>[/TABLE]

{=INDEX($A$2:$F$2,SMALL(IF($A$1:$F$1="Philadelphia",COLUMN($A$1:$F$1),""),2),1)} gets met with a "#REF!" error.

All of the online tutorials that I've been able to locate show me how to work with a vertical list, which I can already do, but I can't see the horizontal equivalent, which seems like it should be easy...

I'm hoping someone can help me out! (Thanks in advance!)

 

[humdingaling]

you've got the index the wrong way round

=INDEX($A$2:$F$2,1,SMALL(IF($A$1:$F$1="Philadelphia",COLUMN($A$1:$F$1),""),2))

the syntax for index is array,row,column
your formula had the array a2:f2
row 5
column 1

a2:f2 only has 1 row...which is why it gives you REF error

if you made the array A2:F10 (or something larger than your small return) it will give you zero instead of an error

as INDEX(A2:F10,5,1) is valid

 

[Jesbass]

you've got the index the wrong way round

=INDEX($A$2:$F$2,1,SMALL(IF($A$1:$F$1="Philadelphia",COLUMN($A$1:$F$1),""),2))

the syntax for index is array,row,column
your formula had the array a2:f2
row 5
column 1

a2:f2 only has 1 row...which is why it gives you REF error

if you made the array A2:F10 (or something larger than your small return) it will give you zero instead of an error

as INDEX(A2:F10,5,1) is valid

Thanks so much! I actually left work just as you posted this so I'll need to check it in the morning but what you say about having the array around the wrong way does make sense!

I wouldn't have thought the use of a single row would matter as my vertical formula only makes use of a single column in each range...have I misinterpreted that part of your post?

Thanks again!

 

[humdingaling]

let me clarify the problem with your formula

=INDEX($A$2:$F$2,SMALL(IF($A$1:$F$1="Philadelphia",COLUMN($A$1:$F$1),""),2),1)

the SMALL() section out of the formula gives you 5
ie the 2nd smallest number out of the array {1,"","","",5,""} is 5

understanding the syntax of INDEX(Array,ROW#,Column#)
if your row# or Column# is greater than your array size you will get a REF# error

in this case your array is A2:F2, so the array size is 1 row, 5 columns
if you then evaluate the formula it gives Index(A2:F2,5,1)
what you actually want is index(A2:F2,1,5)

which is what the formula i posted will give

=INDEX($A$2:$F$2,1,SMALL(IF($A$1:$F$1="Philadelphia",COLUMN($A$1:$F$1),""),2))

 

[Aladin Akyurek]

@Jesbass

{=INDEX($B$1:$B$6,SMALL(IF($A$1:$A$6="Philadelphia",ROW($A$1:$A$6),""),2),1)}

should be:

{=INDEX($B$1:$B$6,SMALL(IF($A$1:$A$6="Philadelphia",ROW($A$1:$A$6)-ROW($A$1)+1),2))}

in order to be robust.

If you are wanting to construct a sublist from B pertaining to the Philadelphia condition...

In E1 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$1:$B$6,SMALL(IF($A$1:$A$6="Philadelphia",ROW($A$1:$A$6)-ROW($A$1)+1),ROWS($E$1:E1))),"")

If the data is in A1:F2...

{=IFERROR(INDEX($A$2:$F$2,SMALL(IF($A$1:$F$1="Philadelphia",COLUMN($A$1:$F$1)-COLUMN($A$1)+1),ROWS($A$4:A4))),"")}

copied down, will build the Philadelphia sublist in A4 and downwards.

{=IFERROR(INDEX($A$2:$F$2,SMALL(IF($A$1:$F$1="Philadelphia",COLUMN($A$1:$F$1)-COLUMN($A$1)+1),COLUMNS($A$4:A4))),"")}

copied across, will build the Philadelphia sublist in A4 and rightwards.