Check for most latest FY and only do lookup to the latest year's rows - Ignore the other years.

[hananak]

Hi All,

I am looking for a formula in excel that will search for the latest year in column "A". Once found then do a lookup to only those rows which have the latest year and ignore the other years rows.

Currently, in column "A", there are three years. 20-21, 21-22 and 22-23 and 22-23 being the latest year. The formula should identify the latest year 22-23 and then do the lookup in column F on the 22-23 rows only and ignore the other years.

If in future the data changes to 20-21, 21-22, 22-23 and 23-24, it should pick 23-24 and do the lookup on 23-24 rows only.

The formula should be written in column E and lookup value is from column E will get it's values from lookup table in another sheet called Table.

Your help would be really appreciated.

 

[awoohaw]

@JIMBO992 , my impression was that there will be values in column E and @hananak also wants a formula in the empty cells of column E. And it should be dynamic, which to means unless VBA is used a pull down (or even Array ) formula will erase existing values or prohibit the the formula in the column.