Pulling the Most Recent Cell from a non-contiguous Range

[Academic_Data]

Hello,

I am working on a tool for teachers to track their students most current reading levels. In column G, I need to pull the most recent cell from a non-contiguous range,

I am attempting to use the lookup formula for this, but I am having trouble stating a non-contiguous range. The formula so far looks like: =lookup(99^99,

The cells that I would like to use in the range are M4, P4, V4, AB4, AH4, and AN4.

I attempted to make another table where adjacent cells equaled these values, but that did not work for my lookup since the blank cell in AN4 was being returned as 0 even though the student had a reading level in AH4.

Any advice?

Thanks!

 

[bbott]

Welcome to the board.

Try:

=CHOOSE(COUNT(M4,P4,V4,AB4,AH4,AN4),M4,P4,V4,AB4,AH4,AN4)

 

[ghrain22]

Define "most recent cell". You can make a dynamic named range using the offset and counta formula.

How to create a dynamic defined range in an Excel worksheet

This might help you get started. Otherwise you can do a lookup against the entire column. However, this is often frowned upon as it greatly increases files size and decreases calculation speed.

 

[Academic_Data]

Define "most recent cell". You can make a dynamic named range using the offset and counta formula.

How to create a dynamic defined range in an Excel worksheet

This might help you get started. Otherwise you can do a lookup against the entire column. However, this is often frowned upon as it greatly increases files size and decreases calculation speed.

So by most recent, I mean furthest to the right.

=choose(count seems to be working. Thanks!

 

[bbott]

So by most recent, I mean furthest to the right.

=choose(count seems to be working. Thanks!

You're welcome.

 

[Academic_Data]

It seems that when using =CHOOSE(COUNT(M4,P4,V4,AB4,AH4,AN4),M4,P4,V4,AB4,AH4,AN4), if one of the preceding values is blank, it will not return the right-most value. With this formula, is it necessary that MP, P4, V4, etc. are all filled in, in order to return AN4?

 

[bbott]

It seems that when using =CHOOSE(COUNT(M4,P4,V4,AB4,AH4,AN4),M4,P4,V4,AB4,AH4,AN4), if one of the preceding values is blank, it will not return the right-most value. With this formula, is it necessary that MP, P4, V4, etc. are all filled in, in order to return AN4?

Yes. You could get around that by putting a 0 in the previous cells. I'll think on it and see if I can get you a better solution.

 

[Academic_Data]

Yes. You could get around that by putting a 0 in the previous cells. I'll think on it and see if I can get you a better solution.

I really appreciate your help, bbott. Putting 0 in the previous cells probably wouldn't work because 0 is considered a reading level. I could foresee some other issues with that as well but I suppose there's always a work around.

Thanks again!

 

[Aladin Akyurek]

Hello,

I am working on a tool for teachers to track their students most current reading levels. In column G, I need to pull the most recent cell from a non-contiguous range,

I am attempting to use the lookup formula for this, but I am having trouble stating a non-contiguous range. The formula so far looks like: =lookup(99^99,

The cells that I would like to use in the range are M4, P4, V4, AB4, AH4, and AN4.

I attempted to make another table where adjacent cells equaled these values, but that did not work for my lookup since the blank cell in AN4 was being returned as 0 even though the student had a reading level in AH4.

Any advice?

Thanks!

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3,4,5,6},M4, P4, V4, AB4, AH4, AN4))

would return the last numeric value from the non-contiguous range.

 

[Academic_Data]

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3,4,5,6},M4, P4, V4, AB4, AH4, AN4))

would return the last numeric value from the non-contiguous range.

Hi Aladin,

This formula seems to be pulling a 0 since AN4 is blank. How might I be able to return the last non-blank cell in the non-contiguous range?