Return Last Value In A Row

[raymaster98]

Using Excel 2003:

I have 2 worksheets.

In worksheet "No Show Data (Current Year)" C29:N29, I have a row of numerical values. There are 12 of them (one for each month). Since it is the current year, not all of them currently have values. B29:N29 contains the months of the year.

In worksheet "No Show Data (Previous Year)" C29:N29, I have another row of numerical values. There are 12 of them (one for each month). Since it is the previous year, they all have values. B29:N29 contains the months of the year.

Currently, Feb is the last month in which there is a value in "No Show Data (Current Year)" C29:N29.

I want to compare the last numerical value of "No Show Data (Current Year)" C29:N29 with the same month from "No Show Data (Previous Year)" C29:N29.

Example:

In "No Show Data (Current Year)" C29:N29, the current values are:

Oct Nov Dec Jan Feb March April May June July Aug Sep
6 6 5 9 16

In "No Show Data (Previous Year)" C29:N29, the current values are:

Oct Nov Dec Jan Feb March April May June July Aug Sep
4 7 8 2 10 3 2 6 8 3 5 7

Since Feb is the most current value, the correct formula would return 10 since it is the value of the same month from the previous year.

I hope this makes sense and I appreciate any help. Thanks, Kenny

 

[Aladin Akyurek]

Try...

=LOOKUP(9.99999999999999E+307,'No Show Data (Current Year)'!C29:N29,'No Show Data (Previous Year)'!C29:N29)

 

[jasonb75]

Maybe

=INDEX('No Show Data (Previous Year)'!B29:M29,,COUNT('No Show Data (Current Year)'!B29:M29))

 

[raymaster98]

Both formulas seem to work great. Thanks to both of you for your expert help. Kenny

 

[Aladin Akyurek]

Both formulas seem to work great. Thanks to both of you for your expert help. Kenny

I don't understand...

COUNT will fail if there is no data for an in-between month. Also, LOOKUP is faster.

 

[jasonb75]

I don't understand...

COUNT will fail if there is no data for an in-between month.

The information in the question would indicate that "in-between" months would be completed and have data.

Also, LOOKUP is faster.

Based on the size of the data range, I doubt anyone would notice.