CODEWITHSUNDEEP
excelexcel-formula
Daruki
Daruki

Reputation: 491

Find row name in matrix using cell value in Excel

Is there a function that outputs the row name given a cell value? I don't use index and match often so it's not very intuitive to me. Any help is appreciated, would rather avoid VBA.

For example in the image, I'd want the function to output "13" if I have a cell reference value 6/16/2016 enter image description here

Upvotes: 0

Views: 935

Answers (3)

pnuts
pnuts

Reputation: 59475

For 1900 date system, if your reference value is in Z1, please try: 

=INT((Z1-42365)/14)+1

Upvotes: 1

Alexis Olson
Alexis Olson

Reputation: 40204

Suppose your row names are in column A:A and your range to search is B2:O27 and the value you want to match is in Z1, then try this formula:

=INDEX($A:$A,MAX(--($B$2:$O$27=$Z$1)*ROW($B$2:$O$27)))

It will need to be input as an array formula using Ctrl+Shift+Enter.

Upvotes: 1

Tom Sharpe
Tom Sharpe

Reputation: 34220

There is also a standard formula for looking up the row in a 2d array using an array formula:-

=MIN(IF($B$2:$O$27=Z1,row($B$2:$O$27)-row($B$2)+1))

must be entered using CtrlShiftEnter

Upvotes: 1

Related Questions