Look up value and return 1 row below-CodePudding

I've been trying to figure out how to find a value in a table and return the string below that. Most solutions I've tried with index don't work, and I don't think the solution is through vlookup.

So to give an example, I want the formula to look at today() and return the value 1 or 2 rows below corresponding to that date (which row is dependent on the AM/PM, but I'm pretty sure I can set that up with Ifs)

Let's say it's 26-08, 1PM then the formula should return 'Eee' as value in the following table:

25-08-2022    26-08-2022  27-08-2022
Aaa           Bbb         Ccc
Ddd           Eee         Fff
28-08-2022    29-08-2022  30-08-2022
Ggg           Hhh         Iii
Jjj           Kkk         Lll

CodePudding user response：

Give a try on below formula-

=INDEX(A1:C6, MAX(ROW(A1:C6)*(A1:C6=E1)) IF(F1="AM",1,2),MAX(COLUMN(A1:C6)*(A1:C6=E1)))