CODEWITHSUNDEEP
excel
Zahid Rouf
Zahid Rouf

Reputation: 1741

Get Column name of specific row and matching column in Excel

I like to get this thing to do:

Input: Subject number , Mark, Output : Grade letter (column name)

Here is the sample excel table:

enter image description here

I have found the row number by using this function: 

=MATCH(C7,A2:A4,0)

Now, how to find the value in that row and get output the column name ?

Upvotes: 2

Views: 6443

Answers (1)

Ken
Ken

Reputation: 1166

If you can change grade range to be increasing from Fail -> A you can use the following formula:

=INDEX(B1:H1,1,MATCH(C8,OFFSET(A2,MATCH(C7,A2:A4,0)-1,1,1,7),1))

(For clarity, the columns would be Fail, D, C-, C, B-, B+, A with the corresponding marks below it) Grade Table

EDIT

After further thought, without changing the table, and avoiding the OFFSET function, the following should work:

=INDEX(B1:H1,COUNTIF(INDEX(B:B,MATCH(C7,A:A)):INDEX(H:H,MATCH(C7,A:A)),">"&C8)+1)

Upvotes: 1

Related Questions