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.

 

[Expanium]

Hi,

I'm facing the same problem as bell0246, but where i work, they still have excel 2007... so the version that doesn't have the function AGGREGATE yet.
I've tried to replace it with SMALL, but keep getting a refference error.

Do you have a sollution for this issue?

FYI: it's about the exact same post as from your last post (2nd of march 2018)

Thanks

 

[Expanium]

Hi,

I'm facing the same problem as bell0246, but where i work, they still have excel 2007... so the version that doesn't have the function AGGREGATE yet.
I've tried to replace it with SMALL, but keep getting a refference error.

Do you have a sollution for this issue?

FYI: it's about the exact same post as from your last post (2nd of march 2018)

Thanks

My bad, not a #REF error, but a #VALUE error is occuring

 

[Peter_SSs]

.. still have excel 2007... so the version that doesn't have the function AGGREGATE yet.
I've tried to replace it with SMALL, but keep getting a refference error.

Do you have a sollution for this issue?

FYI: it's about the exact same post as from your last post (2nd of march 2018)

Welcome to the MrExcel board!

See how this goes.

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		Count	9
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
Headers Multiple

 

[Expanium]

Works like a charm!

Thanks a lot!

 

[Peter_SSs]

Works like a charm!

Thanks a lot!

You are very welcome. Thanks for letting us know.

 

[hkgurl87]

Hi,

I tried to use the solution you provided the April 13th post, but I'm cannot seem to get it to work.

I would like to display the column header that contains "A" in a row form.

For example: Value in A10 would be 1, B10 would equal to B, C10 would equal to E
Value in A11 would be 2, B11 would equal to C, C11 would equal to D, D11 would equal to E, E11 would equal to G
Value in A12 would be 4, B12 would equal to G
Value in A13 would be 6, B13 would equal to D
Value in A14 would be 7, B14 would equal to F

Any help will be greatly appreciate.

 

[Peter_SSs]

Hi,

I tried to use the solution you provided the April 13th post, but I'm cannot seem to get it to work.

I would like to display the column header that contains "A" in a row form.

Welcome to the MrExcel board!

See if this would suffice.
K1 houses the value of interest.
L1 houses a zero.
L2 copied down.
A10 copied down
B10 copied across and down.

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

 

[hkgurl87]

Thank you Peter for the quick response.

B10 works when it's drag across but it doesn't work when it's drag downward.

 

[Peter_SSs]

B10 works when it's drag across but it doesn't work when it's drag downward.

You can see that it has worked for me.

Did you remember to confirm it with Ctrl+Shift+Enter, not just Enter before dragging it anywhere?

Did you modify the formula in any way to suit your circumstances?

 

[hkgurl87]

Hi Peter,

Yes, I did use Ctrl + Shift + Enter, to ensure it's in an array form.

No, I did not modify the formula. However, the values in column A is not a numeric values. it's alphabetic. But I wouldn't think it would make a difference, would it?

For example: Values in A2:A8 is a,b,c,d,e,f,g instead of 1,2,3,4,5,6,7

Regards,
Becki