Thanks:  0
Likes:  0

# Thread: Finding last value in a row

1. 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)

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

3. 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))

4. 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.

5. 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

6. 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.

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

7. 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.

8. 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.
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.

9. 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.
Thank you for the rectification.

10. ## Re: Finding last value in a row

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•