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]

I'm not sure there are mathmatical equations that can determine that 23-24 > 23-22 > 22-21 > 20-21. You may have to input that in the formula when the time comes.
Lookups generally only return one value not a group of values. Are you asking for a filter of only rows with that FY in column A?
Is there data in Col B, C, or D that you want to be returned?

Please install the xl2bb add in and post a mini workbook as a sample of what you want. A sample of the Table would also be useful.

 

[awoohaw]

additionally, to dynamically get the last fy, the values should be numbers, MAX function will not work on text strings.

 

[hananak]

additionally, to dynamically get the last fy, the values should be numbers, MAX function will not work on text strings.

Thank for your quick reply.

Please see the mini sheet.

22-23.xlsx
	A	B	C	D	E
4	Year	Name	Name 2	Acc	Lookup
5	20-21	Data1	Data2	Data3	Ignore as in column A year is 20-21
6	20-21	Data1	Data2	Data3	Ignore as in column A year is 20-21
7	20-21	Data1	Data2	Data3	Ignore as in column A year is 20-21
8	20-21	Data1	Data2	Data3	Ignore as in column A year is 20-21
9	20-21	Data1	Data2	Data3	Ignore as in column A year is 20-21
10	20-21	Data1	Data2	Data3	Ignore as in column A year is 20-21
11	20-21	Data1	Data2	Data3	Ignore as in column A year is 20-21
12	20-21	Data1	Data2	Data3	Ignore as in column A year is 20-21
13	20-21	Data1	Data2	Data3	Ignore as in column A year is 20-21
14	20-21	Data1	Data2	Data3	Ignore as in column A year is 20-21
15	21-22	Data1	Data2	Data3	Ignore as in column A year is 21-22
16	21-22	Data1	Data2	Data3	Ignore as in column A year is 21-22
17	21-22	Data1	Data2	Data3	Ignore as in column A year is 21-22
18	21-22	Data1	Data2	Data3	Ignore as in column A year is 21-22
19	21-22	Data1	Data2	Data3	Ignore as in column A year is 21-22
20	21-22	Data1	Data2	Data3	Ignore as in column A year is 21-22
21	21-22	Data1	Data2	Data3	Ignore as in column A year is 21-22
22	21-22	Data1	Data2	Data3	Ignore as in column A year is 21-22
23	21-22	Data1	Data2	Data3	Ignore as in column A year is 21-22
24	21-22	Data1	Data2	Data3	Ignore as in column A year is 21-22
25	22-23	Data1	Data2	data10	Lookup value in another sheet based on matching value in column D of this sheet only if year is 22-23
26	22-23	Data1	Data2	data11	Lookup value in another sheet based on matching value in column D of this sheet only if year is 22-23
27	22-23	Data1	Data2	data12	Lookup value in another sheet based on matching value in column D of this sheet only if year is 22-23
28	22-23	Data1	Data2	data13	Lookup value in another sheet based on matching value in column D of this sheet only if year is 22-23
29	22-23	Data1	Data2	data14	Lookup value in another sheet based on matching value in column D of this sheet only if year is 22-23
30	22-23	Data1	Data2	data15	Lookup value in another sheet based on matching value in column D of this sheet only if year is 22-23
31	22-23	Data1	Data2	data16	Lookup value in another sheet based on matching value in column D of this sheet only if year is 22-23
32	22-23	Data1	Data2	data17	Lookup value in another sheet based on matching value in column D of this sheet only if year is 22-23
33	22-23	Data1	Data2	data18	Lookup value in another sheet based on matching value in column D of this sheet only if year is 22-23
34	22-23	Data1	Data2	data19	Lookup value in another sheet based on matching value in column D of this sheet only if year is 22-23
35	22-23	Data1	Data2	data20	Lookup value in another sheet based on matching value in column D of this sheet only if year is 22-23
36	22-23	Data1	Data2	data21	Lookup value in another sheet based on matching value in column D of this sheet only if year is 22-23
37	22-23	Data1	Data2	data22	Lookup value in another sheet based on matching value in column D of this sheet only if year is 22-23
38	22-23	Data1	Data2	data23	Lookup value in another sheet based on matching value in column D of this sheet only if year is 22-24
39	22-23	Data1	Data2	data24	Lookup value in another sheet based on matching value in column D of this sheet only if year is 22-25
40	22-23	Data1	Data2	data25	Lookup value in another sheet based on matching value in column D of this sheet only if year is 22-26
41	22-23	Data1	Data2	data26	Lookup value in another sheet based on matching value in column D of this sheet only if year is 22-27
42	22-23	Data1	Data2	data27	Lookup value in another sheet based on matching value in column D of this sheet only if year is 22-28
43	22-23	Data1	Data2	data28	Lookup value in another sheet based on matching value in column D of this sheet only if year is 22-29
44	22-23	Data1	Data2	data29	Lookup value in another sheet based on matching value in column D of this sheet only if year is 22-30
45	22-23	Data1	Data2	data30	Lookup value in another sheet based on matching value in column D of this sheet only if year is 22-31
46	22-23	Data1	Data2	data31	Lookup value in another sheet based on matching value in column D of this sheet only if year is 22-32
47	22-23	Data1	Data2	data32	Lookup value in another sheet based on matching value in column D of this sheet only if year is 22-33
48	22-23	Data1	Data2	data33	Lookup value in another sheet based on matching value in column D of this sheet only if year is 22-34
49	22-23	Data1	Data2	data34	Lookup value in another sheet based on matching value in column D of this sheet only if year is 22-35
50	22-23	Data1	Data2	data35	Lookup value in another sheet based on matching value in column D of this sheet only if year is 22-36
51	22-23	Data1	Data2	data36	Lookup value in another sheet based on matching value in column D of this sheet only if year is 22-37
52	22-23	Data1	Data2	data37	Lookup value in another sheet based on matching value in column D of this sheet only if year is 22-38
53	22-23	Data1	Data2	data38	Lookup value in another sheet based on matching value in column D of this sheet only if year is 22-39
54	22-23	Data1	Data2	data39	Lookup value in another sheet based on matching value in column D of this sheet only if year is 22-40
Trend

 

[awoohaw]

if you do not want existing data in Cells B5:B24 then you'll need some vba to code that.

if you want blanks in E5:E24:

=if(A5="22-23","Lookup Formula","")

put that in E5 and copy down.

 

[hananak]

if you do not want existing data in Cells B5:B24 then you'll need some vba to code that.

if you want blanks in E5:E24:

=if(A5="22-23","Lookup Formula","")

put that in E5 and copy down.

The data in each column is important and I need all the columns data.

Basically, I need two things:

1) I don't want hard coded "22-23" because when the data gets updated in future there will be 23-24 and after 1 year 24-25 and so on.

2) I would like the lookup formula to be dynamic and applicable only to the latest year rows and ignore all the rows where the year is old.

If the data gets updated and now we have 23-24 as well then the formula should automatically find the row from where 23-24 year is starting and apply lookup formula to all the rows which have 23-24 in column A and ignore the rest of the rows where in column A there are other years and not 23-24.

Do you think it is possible?

 

[awoohaw]

You can't have both values and formulas in the same cells. (Column E).
to do what you ask dynamically you would need a vba routine to somehow figure out the highest precedence of the date ranges (A lookup array that would entail many year ranges with a precedence number could be a possibility).
the vba routine would need to find the records with target date range value and insert your data lookup into the cell in column E

 

[hananak]

You can't have both values and formulas in the same cells. (Column E).
to do what you ask dynamically you would need a vba routine to somehow figure out the highest precedence of the date ranges (A lookup array that would entail many year ranges with a precedence number could be a possibility).
the vba routine would need to find the records with target date range value and insert your data lookup into the cell in column E

Is it something you can help with? If you could write array formula or vba code?

 

[awoohaw]

I am not skilled in vba and much more elegant and efficient solutions can be presented by other here.
I can offer a clunky solution with two additional columns (one hidden, one with formulas). And you would have to copy/paste as values from one column to another.
So it would not be very dynamic.

 

[JIMBO992]

Would a separate cell outside of your data range (where you manually select the current financial year), that drives the formula in column E, be acceptable? You can then use an IF statement in column E, as you outlined.

James.