Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 390
Default how to find the right most cell in a row with a value in it ?

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?
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: how to find the right most cell in a row with a value in it ?

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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,124
Default how to find the right most cell in a row 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?


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,346
Default how to find the right most cell in a row with a value in it ?

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?

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 390
Default how to find the right most cell in a row with a value in it ?

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?



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 390
Default how to find the right most cell in a row with a value in it ?

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?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find & Replace: find part cell, replace whole cell katy Excel Worksheet Functions 3 April 3rd 23 01:20 PM
find last cell in range with data, display cell address sevi61 Excel Worksheet Functions 14 October 29th 07 09:36 PM
FIND / SEARCH text compare cell to string in 3rd cell nastech Excel Discussion (Misc queries) 0 October 29th 07 03:51 AM
Find First Non blank cell than find column header and return that value Silver Rose Excel Worksheet Functions 10 April 30th 07 05:56 PM
Change the appearance cell where Find criteria is found in a cell Tomszar Excel Discussion (Misc queries) 3 December 30th 05 03:48 PM


All times are GMT +1. The time now is 09:57 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"