Return all cell addresses that matches value

Exceladd1ct

Board Regular
Joined
Feb 10, 2019
Messages
76
Hello, I have a huge list of items in column A. Many of this items are duplicates(for a good reason).
Is there a way to return all cells that match a certain item?
I use this formula but it only returns the first occurrence:
Code:
 =ADDRESS(MATCH("MyLookup";A1:A10;0);1;4)

Thank you.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
One way
Requires an array formula
- if entered correctly, the formula in the cell is automatically enclosed in { }
- but { } cannot be typed in

With "MyLookup" in cell C1

Formula in D1, confirmed with {CTRL}{SHIFT}{ENTER} and dragged down to D10
=IFERROR(ADDRESS(SMALL(IF($C$1=$A$1:$A$10, ROW($A$1:$A$10)-ROW($A$1)+1), ROW(A1)),1,4),"")

In D12
=TEXTJOIN(",",TRUE,D1:D10)

TEXTJOIN is only available in some of the very latest versions of Excel
- not available in your version if formula returns #NAME?

Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
1
Image
Picture
A2 {=IFERROR(ADDRESS(SMALL(IF($C$1=$A$1:$A$10, ROW($A$1:$A$10)-ROW($A$1)+1), ROW(A1)),1,4),"")}
2
PictureA5
3
LikenessA7
4
PortraitA10
5
Picture
6
Image
7
Picture
8
Likeness
9
Portrait
10
Picture
11
12
A2,A5,A7,A10 =TEXTJOIN(",",TRUE,D1:D10)
13
Sheet: Sheet1
 
Last edited:
Upvote 0
Awesome, this works great. Thank you so much.
I am still curious (it works great the way it is), can it distribute the results (Column D values) horizontally?
 
Upvote 0
Awesome, this works great. Thank you so much.
I am still curious (it works great the way it is), can it distribute the results (Column D values) horizontally?

Replace ROW with COUMN in Yongle formula.

{=IFERROR(ADDRESS(SMALL(IF($C$1=$A$1:$A$10, ROW($A$1:$A$10)-ROW($A$1)+1), COLUMN(A1)),1,4),"")}
 
Upvote 0
one minor amendment required
- replace ROW(A1) with COLUMN(A1)
- and remember to enter as an array formula

=IFERROR(ADDRESS(SMALL(IF($C$1=$A$1:$A$10, ROW($A$1:$A$10)-ROW($A$1)+1), COLUMN(A1)),1,4),"")

EDIT - oops a bit slow on the draw there :oops::oops:
Just spotted @Tom.Jones got in first
 
Last edited:
Upvote 0
Half of the work is already done thanks to you, so i dare to ask one more question.
Considering the previous formula, is it possible to return the group the item is included in, as in the fallowing table?:
ABC
1Item ListLookUp ValueResult
2Group 1item3Group 2, Group 3
3item 1
4item 2
5item 4
6Group 2
7item 3
8item 1
9Group3
10item 6
11item 1
12item 3

<tbody>
</tbody>


Thank you again!
 
Upvote 0
Solution using a helper column as in image below

Formula in B2 and dragged down to B12
=IF(LEFT(A2,5)="Group",A2,B1)

Array formula (commit with {CTRL}{SHIFT}{ENTER} ) in D2 and dragged down to D12
=IFERROR(INDEX($B$1:$B$12,SMALL(IF($C$2=$A$1:$A$12, ROW($A$1:$A$12)-ROW($A$1)+1), ROW(A1))),"")

Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
G
1
Item ListHelperLookUp ValueResult Formula in D2 dragged down
2
Group1Group1item 3Group1 =IFERROR(INDEX($B$1:$B$12,SMALL(IF($C$2=$A$1:$A$12, ROW($A$1:$A$12)-ROW($A$1)+1), ROW(A1))),"")
3
item 1Group1Group3
4
item 2Group1
5
item 4Group1 Formula in B2 dragged down
6
Group2Group2 =IF(LEFT(A2,5)="Group",A2,B1)
7
item 3Group2
8
item 1Group2
9
Group3Group3
10
item 6Group3
11
item 1Group3
12
item 3Group3
13
Sheet: Sheet1
 
Last edited:
Upvote 0
Solution

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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