Finding value in first cell of rows where value appears within an array

[TheBlueMan15]

Hi, sorry that my title probably doesn't fit my question correctly, but I didn't know how to describe it.I have a table of document numbers and the other document numbers that are referenced inside:eg document 1 references documents 2, 3 and 4, document 2 references 1 and 3. What I'm looking for is a formula where I put in the document number and it tells me all the documents that it has been referenced in:eg I put in number 3 and it tells me that it was referenced by document 1 and 2.I tried index matching but was getting nowhere.Thanks in advance for the help!

 

[Marcelo Branco]

Welcome to Mr Excel

To increase the chances of getting help a data sample along with expected results would be helpful.

Take a look at section B in
https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

M.

 

[TheBlueMan15]

Sorry, wasn't sure how to do that! It's for when a document has a change, I need to be able to see how many documents that refer to it are affected. Here is my example table and expected output :

Document #	Reference 1	Reference 2	Reference 3	Reference 4	Reference 5
1	2	3	4	5	6		Changed Document #
2	7	8	9	10	1		4
3	2	4	5	6	7
4	8	9	10	1	2		Documents that contain references to changed document
5	3	4	6	7	8		1
6	9	10	1	2	3		3
7	4	5	6	8	9		5
8	10	1	2	3	4		7
9	5	6	7	8	10		8
10	1	2	3	4	5		10

<colgroup><col width="87" span="7" style="width: 65pt;"><col width="393" style="width: 295pt;"></colgroup><tbody>
</tbody>

 

[Marcelo Branco]

Try

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Document #​	Reference 1​	Reference 2​	Reference 3​	Reference 4​	Reference 5​
2​	1​	2​	3​	4​	5​	6​		Changed Document #​
3​	2​	7​	8​	9​	10​	1​		4​
4​	3​	2​	4​	5​	6​	7​
5​	4​	8​	9​	10​	1​	2​		Documents that contain references to changed document​
6​	5​	3​	4​	6​	7​	8​		1​
7​	6​	9​	10​	1​	2​	3​		3​
8​	7​	4​	5​	6​	8​	9​		5​
9​	8​	10​	1​	2​	3​	4​		7​
10​	9​	5​	6​	7​	8​	10​		8​
11​	10​	1​	2​	3​	4​	5​		10​
12​

<tbody>
</tbody>

Array formula in H6 copied down
=IFERROR(INDEX(A$2:A$11,SMALL(IF(MMULT(--(B$2:F$11=$H$3),{1;1;1;1;1})>0,ROW(A$2:A$11)-ROW(A$2)+1),ROWS(H$6:H6))),"")
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[TheBlueMan15]

Yes that works, thank you so much!

 

[Aladin Akyurek]

Also, in H6 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$A$11,SMALL(IF(ISNUMBER(IF($B$2:$F$11=$H$3,$A$2:$A$11)),ROW($A$2:$A$11)-ROW($A$2)+1),ROWS($H$6:H6))),"")

 

[Marcelo Branco]

Yes that works, thank you so much!

You are welcome. Glad to help.

M.