Finding Header and Row name based on Value

[CoachJava]

I have a table of 1000 students (names in Column A) and roughly 100 courses (in Row 1) and a list of IDs for the students unique to their respective course. I am given an ID for a student and I need to access the students name and the respective course. For instance, I may receive H19 and I need two formulas to show pull the header (course) which would be "SCI" and the student name (row) would be "Hope". I had heard that INDEX would work with either MATCH or SUBPRODUCT. Any help would be greatly appreciated. Thanks.

A B C D E
1 MAT SCI HIS ENG
2 Bret M45 Y42 214 41Q
3 Dani D43 S75 432 78G
4 Matt Q82 E31 229 98U
5 Lynn L41 E85 127 41R
6 Alex K75 M41 985 41I
7 Amie A41 P21 554 86P
8 Kate J84 Y07 882 22N
9 Sean V25 T31 128 14E
10 Hope O94 H19 014 56L
11 Liam E41 S44 047 13M

 

[michaelsmith559]

Here is a macro to do it. You will have to change the ranges to suit your needs.

Here is how I setup sheet 1:

Excel 2012
	A	B	C	D	E	F	G	H	I
1		MAT	SCI	HIS	ENG		Lookup Value	Student	Course
2	Bret	M45	Y42	214	41Q		H19
3	Dani	D43	S75	432	78G
4	Matt	Q82	E31	229	98U
5	Lynn	L41	E85	127	41R
6	Alex	K75	M41	985	41I
7	Amie	A41	P21	554	86P
8	Kate	J84	Y07	882	22N
9	Sean	V25	T31	128	14E
10	Hope	O94	H19	14	56L
11	Liam	E41	S44	47	13M
Sheet1

<p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />

Sheet after macro runs:

Excel 2012
	A	B	C	D	E	F	G	H	I
1		MAT	SCI	HIS	ENG		Lookup Value	Student	Course
2	Bret	M45	Y42	214	41Q		H19	Hope	SCI
3	Dani	D43	S75	432	78G
4	Matt	Q82	E31	229	98U
5	Lynn	L41	E85	127	41R
6	Alex	K75	M41	985	41I
7	Amie	A41	P21	554	86P
8	Kate	J84	Y07	882	22N
9	Sean	V25	T31	128	14E
10	Hope	O94	H19	14	56L
11	Liam	E41	S44	47	13M
Sheet1

<p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />

macro:

Sub Find_Header_and_RowName()
    Dim FindString As String
    Dim Rng As Range
    FindString = Sheets("Sheet1").Range("G2").Value ' Change Where Lookup Value Is As Needed
    If Trim(FindString) <> "" Then
        With Sheets("Sheet1").Range("A1:E11") 'Change Range As Needed
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                Application.Goto Rng, True
                'Change Ranges Below to Where You Want Values Placed
                Sheets("Sheet1").Range("H2").Value = ActiveCell.End(xlToLeft).Value
                Sheets("Sheet1").Range("I2").Value = ActiveCell.End(xlUp).Value
                Range("G" & Rows.Count).End(xlUp).Offset(1, 0).Select
            Else
                MsgBox "Nothing found"
            End If
        End With
    End If
End Sub

 

[CoachJava]

Thanks for the macro. Is there a way to do it without a macro and pull the information formula-driven? I am most concerned about the Column field. Thanks.

 

[Weazel]

since the values are unique maybe something like....

If there is a possibility of duplicates this probably wouldn't work for you

Excel 2012

	A	B	C	D	E	F	G	H
1		MAT	SCI	HIS	ENG		ID
2	Bret	M45	Y42	214	41Q		22N
3	Dani	D43	S75	432	78G		Name	Subject
4	Matt	Q82	E31	229	98U		Kate	ENG
5	Lynn	L41	E85	127	41R
6	Alex	K75	M41	985	41I
7	Amie	A41	P21	554	86P
8	Kate	J84	Y07	882	22N
9	Sean	V25	T31	128	14E
10	Hope	O94	H19	14	56L
11	Liam	E41	S44	47	13M

<tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula G4 {=INDEX(A2:A11,MAX(IF(B2:E11=G2,ROW(A2:A11)-ROW(A2)+1)))} H4 {=INDEX(B1:E1,MAX(IF(B2:E11=G2,COLUMN(B1:E1)-COLUMN(B1)+1)))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[AhoyNC]

These are array formulas and must be entered with CTRL-SHIFT-ENTER (command-return on MAC). Change ranges to match your data and there is a space between the " " in the formula.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1		MAT	SCI	HIS	ENG		Lookup Value	Student	Course
2	Bret	M45	Y42	214	41Q		H19	Hope	SCI
3	Dani	D43	S75	432	78G
4	Matt	Q82	E31	229	98U
5	Lynn	L41	E85	127	41R
6	Alex	K75	M41	985	41I
7	Amie	A41	P21	554	86P
8	Kate	J84	Y07	882	22N
9	Sean	V25	T31	128	14E
10	Hope	O94	H19	14	56L
11	Liam	E41	S44	47	13M
Sheet

 

[Aladin Akyurek]

@CoachJava

Can H19 occur more than once?

 

[Nishant94]

If there is no chance of duplicate

Then, formula in H2 will be =IF($G$2<>"",INDEX($A$2:$A$11,SMALL(IF(ISNUMBER(SEARCH($G$2,$B$2:$E$11,1)),ROW($B$2:$E$11)),1)-1),"")

And formula in I2 will be =IF($G$2<>"",INDEX($B$1:$E$1,SMALL(IF(ISNUMBER(SEARCH($G$2,$B$2:$E$11,1)),COLUMN($B$2:$E$11)),1)-1),"")

If there is a chance of duplicate

Then formula in H2 will be

=IF($G$2<>"",IF(ISERROR(SMALL(IF(ISNUMBER(SEARCH($G$2,$B$2:$E$11,1)),ROW($B$2:$E$11)),ROWS($H$2:H2))-1),"",INDEX($A$2:$A$11,SMALL(IF(ISNUMBER(SEARCH($G$2,$B$2:$E$11,1)),ROW($B$2:$E$11)),ROWS($H$2:H2))-1)),"")

And formula in I2 will be

=IF($G$2<>"",IF(ISERROR(SMALL(IF(ISNUMBER(SEARCH($G$2,$B$2:$E$11,1)),COLUMN($B$2:$E$11)),ROWS($I$2:I2))-1),"",INDEX($B$1:$E$1,SMALL(IF(ISNUMBER(SEARCH($G$2,$B$2:$E$11,1)),COLUMN($B$2:$E$11)),ROWS($I$2:I2))-1)),"")

And then copy/drag the formula down untill you feel it is safe. By that I mean if you feel that there would be maximum of 5 duplicates then copy the formula till H6 and I6 respectively. I have copied the formula till H11 and I11 assuming the maximum number of duplicates to be 10.

 

[Nishant94]

If there is no chance of duplicate

Then, formula in H2 will be =IF($G$2<>"",INDEX($A$2:$A$11,SMALL(IF(ISNUMBER(SEARCH($G$2,$B$2:$E$11,1)),ROW($B$2:$E$11)),1)-1),"")

And formula in I2 will be =IF($G$2<>"",INDEX($B$1:$E$1,SMALL(IF(ISNUMBER(SEARCH($G$2,$B$2:$E$11,1)),COLUMN($B$2:$E$11)),1)-1),"")

If there is a chance of duplicate

Then formula in H2 will be

=IF($G$2<>"",IF(ISERROR(SMALL(IF(ISNUMBER(SEARCH($G$2,$B$2:$E$11,1)),ROW($B$2:$E$11)),ROWS($H$2:H2))-1),"",INDEX($A$2:$A$11,SMALL(IF(ISNUMBER(SEARCH($G$2,$B$2:$E$11,1)),ROW($B$2:$E$11)),ROWS($H$2:H2))-1)),"")

And formula in I2 will be

=IF($G$2<>"",IF(ISERROR(SMALL(IF(ISNUMBER(SEARCH($G$2,$B$2:$E$11,1)),COLUMN($B$2:$E$11)),ROWS($I$2:I2))-1),"",INDEX($B$1:$E$1,SMALL(IF(ISNUMBER(SEARCH($G$2,$B$2:$E$11,1)),COLUMN($B$2:$E$11)),ROWS($I$2:I2))-1)),"")

And then copy/drag the formula down untill you feel it is safe. By that I mean if you feel that there would be maximum of 5 duplicates then copy the formula till H6 and I6 respectively. I have copied the formula till H11 and I11 assuming the maximum number of duplicates to be 10.

Put this formula and you have to press Ctril+shift+enter as it is an array formula. Just pressing enter won't do the job.

 

[AhoyNC]

@Nishant94,
In some cases your formula will return the wrong value. In the OP's example if you enter 14 your search formula will find the 14 in 214 and return Bret instead of Hope.

 

[Nishant94]

Try this this will work.

If there is no chance of duplicate

Then, formula in H2 will be =INDEX($A$2:$A$11,SMALL(IF($G$2=$B$2:$E$11,ROW($B$2:$E$11)),ROWS($H$2:H2))-1)

And formula in I2 will be =INDEX($B$1:$E$1,SMALL(IF($G$2=$B$2:$E$11,COLUMN($B$2:$E$11)),ROWS($I$2:I2))-1)

If there is a chance of duplicate

Then formula in H2 will be

=IF(ISERROR(SMALL(IF($G$2=$B$2:$E$11,ROW($B$2:$E$11)),ROWS($H$2:H2))-1),"",INDEX($A$2:$A$11,SMALL(IF($G$2=$B$2:$E$11,ROW($B$2:$E$11)),ROWS($H$2:H2))-1))

And formula in I2 will be

=IF(ISERROR(SMALL(IF($G$2=$B$2:$E$11,COLUMN($B$2:$E$11)),ROWS($I$2:I2))-1),"",INDEX($B$1:$E$1,SMALL(IF($G$2=$B$2:$E$11,COLUMN($B$2:$E$11)),ROWS($I$2:I2))-1))

And then copy/drag the formula down untill you feel it is safe. By that I mean if you feel that there would be maximum of 5 duplicates then copy the formula till H6 and I6 respectively. I have copied the formula till H11 and I11 assuming the maximum number of duplicates to be 10.