Finding nth Duplicate In A Horizontal List

Jesbass

New Member
Joined
Feb 29, 2016
Messages
4
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):
PhiladelphiaA
Long BeachB
HonoluluC
MiamiD
PhiladelphiaE
Long BeachF

<tbody>
</tbody>

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):
PhiladelphiaLong BeachHonoluluMiamiPhiladelphiaLong Beach
ABCDEF

<tbody>
</tbody>

{=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!)
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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
 
Last edited:
Upvote 0
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!
 
Upvote 0
let me clarify the problem with your formula
Code:
=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
Code:
=INDEX($A$2:$F$2,1,SMALL(IF($A$1:$F$1="Philadelphia",COLUMN($A$1:$F$1),""),2))
 
Upvote 0
@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.
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top