Assume I have the following table in Excel:

A B C

1 2 3

4 5 6

7 8 9

I need to lookup a number, say, 1.1, in the table that has values 1 to 9 and return the corresponding letter. Also, the number I am looking up is unlikely to be exact, so I will need to find the rounded down number. For example, if I have 1.1, I need to find 1 and return A. If the number, when rounded down, cannot be found, then it should not return anything or return "NA".

The context for this is I have a whole mortality table with many death rates. Given a random number (between 0 and 1 inclusive), I need to find this number in the mortality table and return the corresponding year of death (which is in an "earlier" row relative to the mortality rates).

How can I do this? I am thinking index & match, but I am not very good with it... Any intuitive explanations will be greatly appreciated :)

CodePudding user response：

Give a try on below formula-

=INDEX(A1:C1,,MAX(COLUMN(A2:C4)*(A2:C4<=E2)))

You may need array entry the formula for older version of excel. Array entry means confirm formula with CTRL SHIFT ENTER.