Finding last value in a row

**rambollet** · Mar 13th, 2002, 02:25 PM

What formula will return the the first value to the right?

For example: A1=empty cell, B1=3, C1=empty cell, D1=0, E1=5, F1=empty cell.
What does the formula need to be in G1 to return the value of the first cell to the left than contains 0 or a value, or in this case 5 (E1)

**rambollet** · Mar 13th, 2002, 02:26 PM

Please excuse my typo. The question should be: "What forumla will return the first value to the LEFT?"

**Aladin Akyurek** · Mar 13th, 2002, 02:30 PM

On 2002-03-13 14:25, rambollet wrote:
What formula will return the the first value to the right?

For example: A1=empty cell, B1=3, C1=empty cell, D1=0, E1=5, F1=empty cell.
What does the formula need to be in G1 to return the value of the first cell to the left than contains 0 or a value, or in this case 5 (E1)

=INDEX(A1:F1,MATCH(9.99999999999999E+307,A1:F1))

**rambollet** · Mar 13th, 2002, 07:50 PM

Thanks.

Plug in your formula and got the results I needed. Now I just need to spend some time figuring out the formula so I can understand it.

**SamS** · Mar 13th, 2002, 08:03 PM

Aladin, this is the second time you've posted a similar reply and not being a mathematician what is 9.9(al 14 of them)+307 represent?

SamS

**Aladin Akyurek** · Mar 13th, 2002, 11:12 PM

On 2002-03-13 20:03, SamS wrote:
Aladin, this is the second time you've posted a similar reply and not being a mathematician what is 9.9(al 14 of them)+307 represent?

SamS

Sam & Rambollet,

9.99999999999999E+307 is the biggest positive number that Excel knows.

When MATCH (with match type 1) fails to find for a numeric lookup value a matching numeric value in a row or column range to which it's applied, it returns the row (or column) number of the last cell in the range which houses a numeric value.

Given this behavior of MATCH, we feed it that huge number as lookup value for which, failing to find a mathing value, the function is bound to return the row/column number of the last cell of numeric type.

Thus, the formula exploits the fact that there will be practically no numeric value as big as 9.9...E+307 in the range tested.

Aladin

[ This Message was edited by: Aladin Akyurek on 2002-03-13 23:12 ]

**Albert 1** · Mar 14th, 2002, 12:03 AM

On 2002-03-13 23:12, Aladin Akyurek wrote:
.... it returns the row (or column) number of the last cell in the range which houses a numeric value.

More precisely (I think): it doesn't return the row- or columnnumber, but the value of the last cell in the range wich houses a numeric value.
BTW Aladin: your knowledge is admirable!!

**Aladin Akyurek** · Mar 14th, 2002, 01:05 AM

On 2002-03-14 00:03, Albert 1 wrote:

On 2002-03-13 23:12, Aladin Akyurek wrote:
.... it returns the row (or column) number of the last cell in the range which houses a numeric value.

More precisely (I think): it doesn't return the row- or columnnumber, but the value of the last cell in the range wich houses a numeric value.
BTW Aladin: your knowledge is admirable!!

Thanks Albert. MATCH does really return the row/column number of the last numeric cell. When that's fed to INDEX, you get the value the last numeric cell houses. I think you had the latter in mind.

Aladin

**Albert 1** · Mar 14th, 2002, 02:24 AM

On 2002-03-14 01:05, Aladin Akyurek wrote:
Thanks Albert. MATCH does really return the row/column number of the last numeric cell. When that's fed to INDEX, you get the value the last numeric cell houses. I think you had the latter in mind.

You are right Aladin!
Thank you for the rectification.

**Curious Georgian** · Mar 14th, 2014, 03:56 PM

Rehashing an old thread here, but can this formula be altered to find the last text value in a range (similar to the cell references provided above)?
Thanks!
CG