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.

 

[Peter_SSs]

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

No, still works for me. Here it is.

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	a	X	A	X	X	A	X	X				1
3	b	X	X	A	A	A	X	A				2
4	c	X	X	X	X	X	X	X
5	d	X	X	X	X	X	X	A				3
6	e	X	X	X	X	X	X	X
7	f	X	X	X	A	X	X	X				4
8	g	X	X	X	X	X	A	X				5
9
10	a	B	E
11	b	C	D	E	G
12	d	G
13	f	D
14	g	F
15
Headers Multiple (2)

1. Does the column L formula appear to be marking the correct rows?

2. Is the column A formula producing the correct letters in A10:A14?

3. If you still cannot resolve the problem, can you upload your sample file to a file-share site (eg Dropbox) and post a link here to that file?

4. What version of Excel & what operating system are you using?

 

[hkgurl87]

Hi Peter,

It works perfectly now. Thank you so much!!!

 

[hkgurl87]

It was an user error. I selected the incorrect range in the formula in cell B10.

{=IFERROR(INDEX($B$1:$H$1,SMALL(IF(INDEX($B$2:$H$8,MATCH($A10,$A$2:$A$8,0),0)=$K$1,COLUMN($B$1:$H$1)-COLUMN($B$1)+1),COLUMNS($B10:B10))),"")}

Again, thank you for your help.

Cheer!

 

[Peter_SSs]

Again, thank you for your help.

No problem, glad you figured out what the problem was.

 

[p3t0r]

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

Hi Peter, gang!

New member here - found this post from Google which is EXACTLY what I need for my work however, I'm not sure if it's an office 2016 thing, but when I paste the array formula (CTRL + SHIFT + ENTER) for B10, The range (B10:H14) populates with ALL 'B' values. I've tried this both on my work PC and laptop and can't get the values showing.

Any help would be much appreciated!

Regards,
Peter

 

[Peter_SSs]

Welxome to the MrExcel board!

when I paste the array formula (CTRL + SHIFT + ENTER) for B10, The range (B10:H14) populates with ALL 'B' values.

When you did the Ctrl+Shift+Enter, did you have the whole range B10:H14 selected? You should only have B10 selected and then after doing the C+S+E on that cell copy or drag the formula to the other cells.

 

[p3t0r]

When you did the Ctrl+Shift+Enter, did you have the whole range B10:H14 selected? You should only have B10 selected and then after doing the C+S+E on that cell copy or drag the formula to the other cells.

Yep I did all that! Here are my steps:
1) Select range B10:H14,
2) Press F2
3) Pasted the following
=IFERROR(INDEX($B$1:$H$1,SMALL(IF(INDEX($B$2:$H$8,MATCH($A10,$A$2:$A$8,0),0)=$K$1,COLUMN($B$1:$H$1)-COLUMN($B$1)+1),COLUMNS($B10:B10))),"")
4) Press C+S+E

Result is B10:H14 full of B's!

To show it was in fact an array, I attempt to change a value in E12 for example, and I get an error: "You can't change part of an array"

 

[Peter_SSs]

No, that's not what I suggested.

You should only have B10 selected

First remove all the formulas from B10:H14
Now
- Select B10 only
- Paste the formula in that cell only
- F2 (if you are not already in edit mode) and confirm with CSE
- Now copy or drag to the other cells in the B10,H14 range.

 

[p3t0r]

It works! Thank you so much Peter

 

[Peter_SSs]

It works! Thank you so much Peter

You're welcome.