Formula needed: argument changes as more data is added

[BK]

The math is simply the difference between the values in two cells, but one of
the cells will change each time the table is updated.

For example, Row 1 headers are Apr, Mar, Feb, Jan, etc. and the last column
is Prior Year. In At any given time, I want the value for the most recent
month minus the prior year. How do I get it to automatically pick up the
last non-blank cell in the row?

Thanks

 

[Mike H]

Hi,

This will return the contents of the last populated cell in row 2

=LOOKUP(2,1/(2:2<>""),2:2)

or to shorten the range

=LOOKUP(2,1/(A2:M2<>""),A2:M2)

Mike

 

[Mike H]

Hi,

Well we can only guess what result you expect from 'and so on' but there are
6 combinations of yes/no/na and one way is to put them in a 2 column table
like this and I used column L and M. Column L are the combinations and M are
what you want to return.

Col L Col M
yesyes aa
yesno bb
yesNA cc
nono dd
nona ee
nana ff

You can then use vlookup

=VLOOKUP(A2&B2,L1:M6,2,FALSE)

Mike

 

[T. Valko]

Try this:

=LOOKUP(1E100,1:1)

That will return the *last numeric value* from left to right in row 1,
A1:IV1.

 

[Mike H]

OOPs

wrong thread

Hi,

Well we can only guess what result you expect from 'and so on' but there are
6 combinations of yes/no/na and one way is to put them in a 2 column table
like this and I used column L and M. Column L are the combinations and M are
what you want to return.

Col L Col M
yesyes aa
yesno bb
yesNA cc
nono dd
nona ee
nana ff

You can then use vlookup

=VLOOKUP(A2&B2,L1:M6,2,FALSE)

Mike

 

[BK]

Is it possible for it to look from right to left for the last numeric value?

 

[T. Valko]

Let's see what your idea of the *last* number is:

A1.....B1.....C1.....D1
..........10......22..........

Which do you consider to be the *last* number?

The formula I suggested will return 22 as the last number.

 

[BK]

Last number searching from right to left in H - D:

Column Contents of Cell

C Formula in question
D - G No values at this time (future dates)
H Last value entered

 

[T. Valko]

In other words, you want to find the *first* number from left to right:

=INDEX(D1:H1,MATCH(TRUE,INDEX(ISNUMBER(D1:H1),0),0))

 

[Ashish Mathur]

Hi,

Try this array formula (Ctrl+Shift+Enter)

=INDEX(D1:H1,1,MATCH(TRUE,ISNUMBER(D1:H1),0))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com