excelexcel-formula

get respective column value without using vba

I have 3 columns in excel sheet.

col1 col2 col3
1 9:25 9:30
2 9:30 9:35
3 9:35 9:40
4 9:40 9:45
5 9:45 9:50

if I enter time in one cell, i need to get the repsective value of col1. Here the time will not be exact aaaaas given in the col2 and col3, rather it can be any value in the range. i.e; if i enter 9:33, then col1 value equivalent to this time will be "2". I can do it using vba, but I dont want to use it. is there any way of achieving the needful?

Solution

  • So index() with match():

    INDEX($A$3:$A$7,MATCH(E3,$B$3:$B$7,1))

    enter image description here