Return all cell addresses that matches value

Exceladd1ct

Board Regular
Joined
Feb 10, 2019
Messages
64
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.
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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:

Exceladd1ct

Board Regular
Joined
Feb 10, 2019
Messages
64
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?
 

Tom.Jones

Active Member
Joined
Sep 20, 2011
Messages
330
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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),"")}
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:

Exceladd1ct

Board Regular
Joined
Feb 10, 2019
Messages
64

ADVERTISEMENT

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!
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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:
Solution

Watch MrExcel Video

Forum statistics

Threads
1,122,485
Messages
5,596,442
Members
414,065
Latest member
kamlkham

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
Top