return multiple results, but then display the cell reference of each result

[Jali1992]

Hi Everyone, Would you be able to help me create a formula that will allow me to return multiple results, but then display the cell reference of each result

I tried to use INDEX MATCH but it will only display the first match that it finds then return the cell reference of the match, this is the formula that I have got at the moment.

=CELL("address",INDEX($H5:$XFD5,MATCH("*"&$I$1&"*",$H5:$XFD5,0)))

The Issue is that I have multiple cells that match the criteria that I am searching and I need to highlight all the cells within that row that match this.

I have tried to use INDEX SMALL COLUMN functions combined but have had no luck.

=IF(COLUMNS($A4:A4)>$F4,"",INDEX($G4:$DC4,SMALL(IF($G4:$DC4=$I$1,COLUMN($G4:$DC4)-COLUMN($G4)+1),COLUMNS($A4:A4))))

But I have had no luck achieving this

Below is a small example of the data that I am working with

First of all, I am doing a count formula to count the number of time the search criteria appears in the data so that I know how many columns I need.

In the below example there are 2 matches, hence only 2 columns, in the real example there might be 10+

Then in Cell "A3" I need the formula to pull out the first match and returns the cell reference and "B3" to show the second match etc.


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Search Criteria[/TD]
[TD]1617940987[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Cell Ref1[/TD]
[TD]Cell Ref 2[/TD]
[TD]Count If Formula[/TD]
[TD]Number 1[/TD]
[TD]Number 2[/TD]
[TD]Number 3[/TD]
[TD]Number 4[/TD]
[TD]Number 5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]=COUNTIF($D5:$H2,"*"&$E$1&"*")[/TD]
[TD]79081617940987[/TD]
[TD]10298764[/TD]
[TD]1235333[/TD]
[TD]01617940987123[/TD]
[TD]1245632[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]












Please let me know if this is possible.

If you need any further details I am happy to explain further,

Thank You In advance.

 

[Jali1992]

one thing to note is that the numbers aren't always in the same format, for example Cell E4 might contain data like this "GYHU@1617940987" but the formula still needs to pick this up

 

[Aladin Akyurek]

Care to post a small sample along with the desired results (with no reference to a possible formula).

 

[Jali1992]

Hi Aladin Akyurek,

Below is a small sample along with the desired results, so in the below example I am looking for anything with the cell that contains the word "Test1" regardless of any prefix or suffix that has been added.

If possible I would still require the count in the first column as this will let me know how many columns I need to insert, and the formula just needs to return the cell reference where the search criteria match.

[TABLE="width: 854"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Count[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]A_NUMBER[/TD]
[TD]AMA_B_NUMBER[/TD]
[TD]B_NUMBER[/TD]
[TD]C_NUMBER[/TD]
[TD]B_NUM[/TD]
[TD]C_NUM[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]G3[/TD]
[TD]I3[/TD]
[TD]J3[/TD]
[TD]K3[/TD]
[TD] [/TD]
[TD]Test1[/TD]
[TD]Test2[/TD]
[TD]Test1[/TD]
[TD]123Test1[/TD]
[TD]Test1324[/TD]
[TD]31Test214[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]G4[/TD]
[TD]K4[/TD]
[TD]L4[/TD]
[TD][/TD]
[TD][/TD]
[TD]123Test1[/TD]
[TD]123Tst1[/TD]
[TD]Test2[/TD]
[TD]Test991[/TD]
[TD]123Test1[/TD]
[TD]Test1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]G5[/TD]
[TD]H5[/TD]
[TD]I5[/TD]
[TD]K5[/TD]
[TD]L5[/TD]
[TD]Test1[/TD]
[TD]Test1[/TD]
[TD]123Test1[/TD]
[TD]31Test214[/TD]
[TD]123Test1[/TD]
[TD]123Test1[/TD]
[/TR]
</tbody>[/TABLE]


Hope this is enough detail for you.****** id="cke_pastebin" style="position: absolute; top: 176px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 854"]
<tbody>[TR]
[TD]K4[/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[Aladin Akyurek]

Thanks for the Excel readable data and desired results...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][/tr][tr][td]

2​

[/td][td]Count[/td][td]Test1[/td][td][/td][td][/td][td][/td][td][/td][td]A_NUMBER[/td][td]AMA_B_NUMBER[/td][td]B_NUMBER[/td][td]C_NUMBER[/td][td]B_NUM[/td][td]C_NUM[/td][/tr]
[tr][td]

3​

[/td][td]

4​

[/td][td]$G$3[/td][td]$I$3[/td][td]$J$3[/td][td]$K$3[/td][td][/td][td]Test1[/td][td]Test2[/td][td]Test1[/td][td]123Test1[/td][td]Test1324[/td][td]31Test214[/td][/tr]
[tr][td]

4​

[/td][td]

3​

[/td][td]$G$4[/td][td]$K$4[/td][td]$L$4[/td][td][/td][td][/td][td]123Test1[/td][td]123Tst1[/td][td]Test2[/td][td]Test991[/td][td]123Test1[/td][td]Test1[/td][/tr]
[tr][td]

5​

[/td][td]

5​

[/td][td]$G$5[/td][td]$H$5[/td][td]$I$5[/td][td]$K$5[/td][td]$L$5[/td][td]Test1[/td][td]Test1[/td][td]123Test1[/td][td]31Test214[/td][td]123Test1[/td][td]123Test1[/td][/tr]
[/table]

In A3 enter and copy down:

=COUNTIFS(G3:L3,"*"&B$2&"*")

In B3 control+shift+enter, not just enter, copy across to L3, and down:

=IFERROR(CELL("address",INDEX($G3:$L3,SMALL(IF(ISNUMBER(SEARCH($B$2,$G3:$L3)),COLUMN($G3:$L3)-COLUMN($G3)+1),COLUMNS($B3:B3)))),"")

If you want the cell addresses in a relative form, implement...

=IFERROR(SUBSTITUTE(CELL("address",INDEX($G3:$L3,SMALL(IF(ISNUMBER(SEARCH($B$2,$G3:$L3)),COLUMN($G3:$L3)-COLUMN($G3)+1),COLUMNS($B3:B3)))),"$",""),"")

 

[Jali1992]

Thank you soo much, this formula works exactly as I need it to work, I think the issue with my formula was that the small was looking for a number value hence why I was getting errors, where are formula you made uses the ISNUMBER(SEARCH() to enable the checks to be carried out.
Thanks for your help Aladin. Really appreciate it!!

 

[Aladin Akyurek]

@ Jali1992

You are welcome. Thanks for the update.