Find Value in Table and Return Column Header/Row Header for Each Occurrence

[jrussell19]

Hi all,

I'll simplify the table to help, but effectively I need to return the Column Header and Row Header of each occurrence of a specific value in a table. i.e.

Value: A

A B C D E F G
1 X X X X A X X
2 X X A X X X X
3 X X X X X X X
4 X X X X X X A
5 X X X X X X X
6 X X X A X X X
7 X X X X X A X

This needs to return:
Row Header Column Header
1 E
2 C
4 G
6 D
7 F

The initial table does not have a defined amount of rows. Additional rows will be added throughout the year.

I've been pulling my hair out with match,index,offset etc but can't for the life of me work it out! Any help would be absolutely amazing!

Thanks very much.

 

[jrussell19]

Sorry Peter, i'll try and explain a little better. See sample table below;

Specified Value: 5

_ | A | B | C | D | E | F | G
1 | 4 | 5 | 3 | 9 | 5 | 4 | 7
2 | 1 | 8 | 3 | 9 | 6 | 6 | 3
3 | 3 | 8 | 9 | 5 | 9 | 2 | 6
4 | 8 | 6 | 9 | 7 | 3 | 6 | 7

Where any value is less than 5, return row/column header.

Answer:
1 | A
1 | C
1 | F
2 | A
2 | C
2 | G
3 | A
3 | F
4 | E

Once again, thanks for your help.

 

[Peter_SSs]

I would not be attempting to do that without helper columns.

L1 houses a 0
L2 copied down to the last row of the main table.
M2 (Ctrl+Shift+Enter) copied across to column S (ie 7 columns - the same as the main data) and down to the last row of the main table.
A8 and B8 copied down as far as you might ever need.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1		A	B	C	D	E	F	G		Value:	5	0
2	1	4	5	3	9	5	4	7				3	A	C	F
3	2	1	8	3	9	6	6	3				6	A	C	G
4	3	3	8	9	5	9	2	6				8	A	F
5	4	8	6	9	7	3	6	7				9	E
6
7
8	1	A
9	1	C
10	1	F
11	2	A
12	2	C
13	2	G
14	3	A
15	3	F
16	4	E
17
Headers 3

 

[ninaking]

I'm trying to do something similar but have more than one column that could have the value A. How would that formula need to be updated if the value was returned in more than one column per row? Meaning if there was "A" in columns D and E?

B10	=IF(A10="","",INDEX(B$1:H$1,MATCH(K$1,INDEX(B$2:H$8,MATCH(A10,A$2:A$8,0),0),0)))

<tbody>


</tbody>

 

[Peter_SSs]

I'm trying to do something similar but have more than one column that could have the value A. How would that formula need to be updated if the value was returned in more than one column per row? Meaning if there was "A" in columns D and E?

B10	=IF(A10="","",INDEX(B$1:H$1,MATCH(K$1,INDEX(B$2:H$8,MATCH(A10,A$2:A$8,0),0),0)))

That would require a very minor change to the formulas in L2 & M2 of post #12. So follow the guidance there, with the formulas below, and see how it goes.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1		A	B	C	D	E	F	G		Value:	5	0
2	1	4	5	3	9	5	4	7				2	B	E
3	2	1	8	3	9	6	6	3				2
4	3	5	8	9	5	5	2	6				5	A	D	E
5	4	8	6	9	7	3	6	5				6	G
6
7
8	1	B
9	1	E
10	3	A
11	3	D
12	3	E
13	4	G
14
Headers

 

[MikeHof]

I have used these formula's on my spreadsheet but am having a couple of issues. The formula for M2 thru S5 returns the correct # of instances in the range I am looking for but the column headers are showing up as #N/A. All cells are set to general format. Also if there are multiple instances of a date being less than today can I have the row header just show up once? These formulas have been a great help as I've been trying to figure something like this out for the past few days

I would not be attempting to do that without helper columns.

L1 houses a 0
L2 copied down to the last row of the main table.
M2 (Ctrl+Shift+Enter) copied across to column S (ie 7 columns - the same as the main data) and down to the last row of the main table.
A8 and B8 copied down as far as you might ever need.

Headers 3

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1		A	B	C	D	E	F	G		Value:	5	0
2	1	4	5	3	9	5	4	7				3	A	C	F
3	2	1	8	3	9	6	6	3				6	A	C	G
4	3	3	8	9	5	9	2	6				8	A	F
5	4	8	6	9	7	3	6	7				9	E
6
7
8	1	A
9	1	C
10	1	F
11	2	A
12	2	C
13	2	G
14	3	A
15	3	F
16	4	E
17

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:35px;"><col style="width:35px;"><col style="width:35px;"><col style="width:35px;"><col style="width:35px;"><col style="width:35px;"><col style="width:35px;"><col style="width:35px;"><col style="width:35px;"><col style="width:56px;"><col style="width:35px;"><col style="width:35px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula L2 =COUNTIF($B$2:H2,"<"&K$1) M2 {=IF(COLUMNS($M2:M2)>($L2-$L1),"",INDEX($B$1:$H$1,SMALL(IF($B2:$H2<$K$1,COLUMN($B2:$H2)-COLUMN($B2)+1),COLUMNS($M2:M2))))} A8 =IF(ROWS(A$8:A8)>L$5,"",INDEX(A$2:A$5,MATCH(ROWS(A$8:A8)-1,L$1:L$5,1))) B8 =IF(A8="","",INDEX($M$2:$S$5,MATCH(A8,A$2:A$5,0),COUNTIF(A$8:A8,A8))) <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[forest1959]

Fantastic walkthrough Peter_SSs. 4 years on and this thread is still providing help to idiots like me... I managed to use your example in a spreadsheet I've been struggling with for some time until i stumbled across this post.

Thank you
Regards
Paul

 

[bell0246]

Hi,

How does the formula need to be updated if the value in question (A in the example) occurs more than once on a given row? Preferably without using helper columns.

Thanks

 

[Peter_SSs]

How does the formula need to be updated ...

Which formula?

There have been several questions and several answers in this thread so far, going back 5 years. Which question and which answer are you referring to?

 

[bell0246]

I'm referring to the very first question of trying return the column header/row header for each occurrence of a specific value in a table, but when the value occurs more than once in the same row and column. The formula below works except when the value occurs more than once in the same row.

A10	{=IF(ROWS(A$10:A10)>COUNTIF($B$2:$H$8,K$1),"",INDEX(A$2:A$8,SMALL(IF($B$2:$H$8=K$1,ROW($B$2:$H$8)-ROW($B$2)+1),ROWS(A$10:A10))))}
B10	=IF(A10="","",INDEX(B$1:H$1,MATCH(K$1,INDEX(B$2:H$8,MATCH(A10,A$2:A$8,0),0),0)))

<tbody>
</tbody>

Thank you

 

[Peter_SSs]

Is this what you mean?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1		A	B	C	D	E	F	G		Value:	A
2	1	X	A	X	X	A	X	X
3	2	X	X	A	A	A	X	A
4	3	X	X	X	X	X	X	X
5	4	X	X	X	X	X	X	A
6	5	X	X	X	X	X	X	X
7	6	X	X	X	A	X	X	X
8	7	X	X	X	X	X	A	X
9
10	1	B
11	1	E
12	2	C
13	2	D
14	2	E
15	2	G
16	4	G
17	6	D
18	7	F
19
Headers Multiple