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.

	A	B	C	D	E	F	G	H
1				Search Criteria	1617940987
2	Cell Ref1	Cell Ref 2	Count If Formula	Number 1	Number 2	Number 3	Number 4	Number 5
3			=COUNTIF($D5:$H2,"*"&$E$1&"*")	79081617940987	10298764	1235333	01617940987123	1245632
4
5

<tbody>
</tbody>

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.

Count						A_NUMBER	AMA_B_NUMBER	B_NUMBER	C_NUMBER	B_NUM	C_NUM
4	G3	I3	J3	K3		Test1	Test2	Test1	123Test1	Test1324	31Test214
3	G4	K4	L4			123Test1	123Tst1	Test2	Test991	123Test1	Test1
5	G5	H5	I5	K5	L5	Test1	Test1	123Test1	31Test214	123Test1	123Test1

<colgroup><col><col><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

Hope this is enough detail for you.****** id="cke_pastebin" style="position: absolute; top: 176px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

K4

<tbody>
</tbody>

</body>

 

[Aladin Akyurek]

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

Row\Col

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

2​

Count

Test1

A_NUMBER

AMA_B_NUMBER

B_NUMBER

C_NUMBER

B_NUM

C_NUM

3​

4​

$G$3

$I$3

$J$3

$K$3

Test1

Test2

Test1

123Test1

Test1324

31Test214

4​

3​

$G$4

$K$4

$L$4

123Test1

123Tst1

Test2

Test991

123Test1

Test1

5​

5​

$G$5

$H$5

$I$5

$K$5

$L$5

Test1

Test1

123Test1

31Test214

123Test1

123Test1

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.