I have a need to have cell with a formula or series of formulas, that can
find the right most cell in a specific row that has a value in it.

I tried various flavors of lookup but was not able to make this work.

any ideas?

Finding the Right Most Cell in a Row with a Value in Excel

1. Select the cell where you want to display the result.

1. Use the following formula:
   Formula:

   =LOOKUP(2,1/(A1:X1<""),COLUMN(A1:X1)) 

2. Replace A1:X1 with the range of cells in the row that you want to search.
3. Press Enter to see the result.

This formula works by first checking if each cell in the specified row is not empty. If a cell is not empty, the formula returns the column number of that cell. The LOOKUP function then finds the largest value in the array of column numbers, which is the right most cell with a value in it.

Note that this formula assumes that there is only one cell with a value in the specified row. If there are multiple cells with values, the formula will return the column number of the right most cell with a value in it.

This finds the column num


=MATCH(LOOKUP(2,1/(14:14<""),14:14),14:14)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Max" wrote in message
...
I have a need to have cell with a formula or series of formulas, that can
find the right most cell in a specific row that has a value in it.

I tried various flavors of lookup but was not able to make this work.

any ideas?

Depends on what you mean by "value"

=LOOKUP(9^9,3:3)

returns the last number in row 3

=LOOKUP(REPT("z",255),3:3)

returns the last text entery on row 3

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

returns the last non-blank cell.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Max" wrote:

I have a need to have cell with a formula or series of formulas, that can
find the right most cell in a specific row that has a value in it.

I tried various flavors of lookup but was not able to make this work.

any ideas?

sorry, yes I should have been more specific:

I have a row, formatted as numeric. Everytime a certian event happens that I
am tracking at work, I put a number in the next column to the right.

On another location in the worksheet, I need to have a forumula that will
look in the specific row and find the number, furthest to the right. They
are not in any kind of order that would allow a min, or max function to be
used.

I will try the lookup formula you provided.

Thanks !

"Shane Devenshire" wrote:

Depends on what you mean by "value"

=LOOKUP(9^9,3:3)

returns the last number in row 3

=LOOKUP(REPT("z",255),3:3)

returns the last text entery on row 3

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

returns the last non-blank cell.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Max" wrote:

I have a need to have cell with a formula or series of formulas, that can
find the right most cell in a specific row that has a value in it.

I tried various flavors of lookup but was not able to make this work.

any ideas?

The formula =lookup(9^9,3:3) works, with "3" being the row number.

questions:
1- what is the 9^9 ?
2- can i specify this for certian columns in a row? for example, look in row
B37:AH37 ?

"Shane Devenshire" wrote:

Depends on what you mean by "value"

=LOOKUP(9^9,3:3)

returns the last number in row 3

=LOOKUP(REPT("z",255),3:3)

returns the last text entery on row 3

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

returns the last non-blank cell.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Max" wrote:

I have a need to have cell with a formula or series of formulas, that can
find the right most cell in a specific row that has a value in it.

I tried various flavors of lookup but was not able to make this work.

any ideas?