Lookup last entry on a row referencing every second column

[ozbeachbum]

I am trying to return the last entry in a row, but only referencing every second column.

Destination Cell AG76 Tue 02-Jul-41 17:30
Reference Cells NO76 Mon 01-Jul-41 12:00 NP $10.00 NQ76 Tue 02-Jul-41 17:30 NR $10.50
Any assistance greatly appreciated.

240905 20 CHRONICLE lup2ndc.xlsx
	AG	AH	AI	AJ	AK																																																																																																																																																																																																																																																																																																																																																				NM	NN	NO	NP	NQ	NR
74	Lookup every second column starting at "NO"
75
76	Tue 02-Jul-41 17:30																																																																																																																																																																																																																																																																																																																																																										Mon 01-Jul-41 12:00	$ 10.00	Tue 02-Jul-41 17:30	$ 10.50
Blank

 

[myall_blues]

Does this work for your situation?
Change the 'NZ' column reference to wherever you need it to extend to.

=IF(MOD(COLUMN(INDIRECT(CELL("address",INDEX(NO76:NZ76,1,COUNTA(NO76:NZ76))))),2)=1,INDEX(NO76:NZ76,1,COUNTA(NO76:NZ76)),INDEX(NN76:NZ76,1,COUNTA(NN76:NZ76)))

 

[ozbeachbum]

Thanks again Murray,
Worked perfectly, just had to add the following to eliminate SPILL on blank rows.

=INDEX(IF(MOD(COLUMN(INDIRECT(CELL("address",INDEX(NO76:NZ76,1,COUNTA(NO76:NZ76))))),2)=1,INDEX(NO76:NZ76,1,COUNTA(NO76:NZ76)),INDEX(NN76:NZ76,1,COUNTA(NN76:NZ76))),1)

Cheers,
Dave.

 

[Peter_SSs]

You have not given us a lot of information about the rest of the relevant data columns but I would be fairly surprised if it could not be done much shorter and without all those volatile functions (CELL, INDIRECT) in there.

Some possibilities, but there may be others depending on just what might happen in those columns.

If there are headings somewhere in those date columns and there is anything in those headings to identify the date columns (or the Amount columns). For example, if all the date columns had a heading of "Date" in row 1 and any dates across the data row are increasing like your one example (several examples usually helps more )

=MAXIFS(NO76:NZ76,NO1:NZ1,"Date")

Same as above but the data row dates may not be increasing

=LET(r,NO76:NZ76,f,FILTER(r,(NO1:NZ1="Date")*(r<>""),NA()),IFNA(INDEX(f,COLUMNS(f)),0))

If the Date/Amount values always come in pairs (whether or not there are headings)

=IFNA(LOOKUP(9^9,NO76:NZ76,NN76:NY76),0)

No ifs

=IFNA(LOOKUP(9^9,INDEX(NO76:NZ76,SEQUENCE(,COLUMNS(NO76:NZ76)/2,,2))),0)

 

[ozbeachbum]

You have not given us a lot of information about the rest of the relevant data columns but I would be fairly surprised if it could not be done much shorter and without all those volatile functions (CELL, INDIRECT) in there.

Some possibilities, but there may be others depending on just what might happen in those columns.

If there are headings somewhere in those date columns and there is anything in those headings to identify the date columns (or the Amount columns). For example, if all the date columns had a heading of "Date" in row 1 and any dates across the data row are increasing like your one example (several examples usually helps more )

=MAXIFS(NO76:NZ76,NO1:NZ1,"Date")

Same as above but the data row dates may not be increasing

=LET(r,NO76:NZ76,f,FILTER(r,(NO1:NZ1="Date")*(r<>""),NA()),IFNA(INDEX(f,COLUMNS(f)),0))

If the Date/Amount values always come in pairs (whether or not there are headings)

=IFNA(LOOKUP(9^9,NO76:NZ76,NN76:NY76),0)

No ifs

=IFNA(LOOKUP(9^9,INDEX(NO76:NZ76,SEQUENCE(,COLUMNS(NO76:NZ76)/2,,2))),0)

Thanks Peter,
Take your point re examples, and will do better in the future.
I have gone with MAXIFS(NO76:NZ76,NO1:NZ1,"Date") as I also need to bring in the $ value on the same row, so using the header works well.
Your knowledge is amazing.
Cheers,
Dave.

 

[Peter_SSs]

If the date & amount always come in pairs then for amount all you should need is

=IFNA(LOOKUP(9^9,NO76:NZ76),0)

 

[ozbeachbum]

If the date & amount always come in pairs then for amount all you should need is

=IFNA(LOOKUP(9^9,NO76:NZ76),0)

Thanks,
That's great.