Return Last Entry from multiple column from successful index

[jchungerford]

Hi,

I am attempting to index multiple columns in succession and return the last entry from every nth row. Every index variation I attempt fails. Anyone more educated than I can lend a hand?

GOAL = Index every month column in succession (Jan, then Feb, then Mar, then Apr, etc etc through Dec) and return the very last entry from every 2nd and 4th within range ROW8:ROW98 and ignoring every 3rd row and blank cells.

Attached is the spreadsheet.

Thanks in advance.

[CK76]

Try below (though I'm sure there's something more efficient...)

Please Login or Register  to view this content.

Confirmed as array (CTRL + SHIFT + ENTER).

[jchungerford]

Not quite. The formula needs to product the LAST number in the overall index, not the largest. The result can be any numerical value within the range.

[CK76]

Hmm? The formula takes largest row index and column index, not the largest numeric value.
Edit: It first looks for largest column index which returns numeric value in range. Then using that column index, looks for largest row which has numeric value (which also matches to rows containing numeric value in E8:E100 range.)

0.JPG

[jchungerford]

NVm. I'm sorry. I was confused on the result. This works well actually. Thanks so much CK76!

[FlameRetired]

Edited Never mind. I didn't see the insets in column T:Z.

I am confused:

GOAL = Index every month column in succession (Jan, then Feb, then Mar, then Apr, etc etc through Dec) and return the very last entry from every 2nd and 4th within range ROW8:ROW98 and ignoring every 3rd row and blank cells.

That is a strange pattern; every 2nd and 4th is Close, Open and Change in the first 6 rows. Cell J13 would be both 3rd and 4th. What am I missing? Do you mean 1st and 2nd ignoring 3rd?

Row\Col	J
3
4	April
5
6	$70.91
7	171.13%
8	$76.62
9	$67.74
10	-11.59%
11	$67.74
12	$69.92
13	3.22%
14	$69.92
15	$57.42
16	-17.88%
17	$57.42
18	$65.49
19	14.05%
20	$65.49
21	$71.81
22	9.65%

[jchungerford]

I apologize. I was trying to write to different questions and mixed them up. :|

Yes, every 1st and 2nd (accounting values), ignoring every third (percentages). Sorry for the confusion, been a rough weekend and I'm all over the place.

[AlKey]

Here are couple of other thing to consider:
Replace your long AVERAGE formula in G6
Enter in G6 and copy across

Please Login or Register  to view this content.

For the last number use this

Please Login or Register  to view this content.