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

jrussell19

New Member
Joined
May 16, 2013
Messages
7
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.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Code:
Function MyROW(MyVAL)    With ActiveSheet.UsedRange
        FFIND = False
        For J = .Column + 1 To .Column + .Columns.Count
            For I = .Row + 1 To .Row + .Rows.Count
                If (Cells(I, J) = MyVAL) Then
                    FFIND = True
                    GoTo MyEND
                End If
            Next I
        Next J
MyEND:
        If (FFIND) Then MyROW = Cells(I, .Column)
    End With
End Function
Function MyCOLUMN(MyVAL)
    With ActiveSheet.UsedRange
        FFIND = False
        For J = .Column + 1 To .Column + .Columns.Count
            For I = .Row + 1 To .Row + .Rows.Count
                If (Cells(I, J) = MyVAL) Then
                    FFIND = True
                    GoTo MyEND
                End If
            Next I
        Next J
MyEND:
        If (FFIND) Then MyCOLUMN = Cells(.Row, J)
    End With
End Function
 
Upvote 0
Hi Vikas,

Thanks for replying.

Although I wish I knew how VBA worked, I barely have any idea how to apply that formula.

Is there a way of using formulas to get to the same result?

Thanks in advance.
 
Upvote 0
Hello,

I am able to provide formulas, however I have not yet worked out how to list only those with matches. But hopefully it will give you a start.

With K for the row header and L for the column header, paste these from row 2 and copy down:

K2 =IF(L2="","",A2)
L2 =IF(ISERROR(MATCH($J$2,B2:H2,0)),"",INDEX($B$1:$H$1,1,MATCH($J$2,B2:H2,0)))
 
Last edited:
Upvote 0
Hello again,

I realised that I've used the column and row headers as part of the table in error. Please ignore my previous formulas (unless they work for you :smile:). Use these instead, and copy down:

J1 =IF(K1="","",ROW(K1))
K1 =LEFT(IF(ISERROR(MATCH($I$1,A1:G1,0)),"",ADDRESS(ROW(),MATCH($I$1,A1:G1,0),4)),1)

I still have spaces where they don't match though.
 
Upvote 0
Is it possible the the value in question (A in the example) could occur more than once on a given row, meaning the results might need to be like this?

1 E
1 F
1 G
2 C
4 G
etc
 
Upvote 0
miss_ell: Thanks, this is definitely further than I got! As you say it's just the spaces.

Peter_SSs: The value will only ever occur once on each row, however it could occur more than once on each column.

Thanks for your help! Slowly getting there!
 
Upvote 0
Formulas in A10 & B10 copied down as far as you might ever need. That is, 7 rows for this sample data.

A10 is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel Workbook
ABCDEFGHIJK
1ABCDEFGValue:A
21XXXXAXX
32XXAXXXX
43XXXXXXX
54XXXXXXA
65XXXXXXX
76XXXAXXX
87XXXXXAX
9
101E
112C
124G
136D
147F
15
16
Headers 1




If you wanted to keep the individual formulas a bit simpler, and were happy to use some helper columns (that could be hidden), then here is an alternative.

L1 houses a 0, or is empty.
L2 & M2 copied down to the last row of the table.
A10 & B10 again copied down as far as needed.

Excel Workbook
ABCDEFGHIJKLM
1ABCDEFGValue:A0
21XXXXAXX15
32XXAXXXX23
43XXXXXXX2
54XXXXXXA37
65XXXXXXX3
76XXXAXXX44
87XXXXXAX56
9
101E
112C
124G
136D
147F
15
16
Headers 2
 
Upvote 0
Hi Peter,

This is perfect - thank you very much!

My follow up question however, is a bit more tricky... but i'm sure you can do it (as I can't get my head around it!)

The table I use is filled with dates, so to translate from the above: Any date where it matches the specified date, return row/column header.

But for the next step I need it to do: Any date where it is less than the specified date, return row/column header.

I've managed to get to the point where it can list the row headers, by changing the the countif/if formulas to "<". But now I can't return the column header due to there being more than one occurrence on each row (as you mentioned previously).

Thank you very much in advance!

FYI: I used the 1st formulae without the helper columns.
 
Upvote 0
But for the next step I need it to do: Any date where it is less than the specified date, return row/column header.

... But now I can't return the column header due to there being more than one occurrence on each row
Not clear. The red is singular, the blue is plural.

What is to be returned if more than one (or even all 7) dates in the row are less than the specified date being used as a comparison?
Some sample data and expected results may help clarify.
 
Upvote 0

Forum statistics

Threads
1,224,827
Messages
6,181,197
Members
453,022
Latest member
RobertV1609

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top