How to Check if exists value and return cell

**virtualdark** · 12-15-2011, 06:18 AM

I have this problem... I have a worksheet that needs to be analised and then return the last date that has some kind of value

.......A.................B.................C.................D.................E.....................F
1/12/2011 | 2/12/2011 | 3/12/2011 | 4/12/2011 | 5/12/2011 | 6/12/2011 | ... ...
.........................75%.............32%

Has you can see cell A it's empty bellow the date, cell B has value and C has value, the other don't, but they will have when there is value for that day
In this case the last cell with value it's C

Let's say that in X cell I need to have a formula (that cannot be VBA) that I would have to return the date: 3/12/2011 because it's the last one that has value bellow.
How can I do that? I was using ISBLANK... but got nowere.

Can you help please?

**arthurbr** · 12-15-2011, 06:57 AM

Supposing your dates are in row1 and values in row 2, try

Please Login or Register  to view this content.

**sweep** · 12-15-2011, 07:00 AM

Hi,

This will return the value in row 1 adjacent to the rightmost cell in row 2 that is not blank

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

**virtualdark** · 12-15-2011, 07:23 AM

Greetings... I have corrected the "design" :-) Cell A2 it's empty

**virtualdark** · 12-15-2011, 07:29 AM

My Excel it's having trouble accepting that 9.99999999999999E+307 !!! what am I doing wrong?

**arthurbr** · 12-15-2011, 07:36 AM

Maybe you need to use a comma after the first 9 instead of a dot ( regional settings)
Otherwise Sweep's formula will also do

**virtualdark** · 12-15-2011, 07:42 AM

Ok... it was yhe comma... geeesh I'm slow today :-)

I forgot to say something
.......A.................B............C.................D.............E................ .F.....
1/12/2011 | 2/12/2011 | 3/12/2011 | 4/12/2011 | 5/12/2011 | 6/12/2011 | ... ...
.....................75%.............32%............................................10%....

If I have a weekend I find no value and it jumps forward... so in this case the last one it's F with 10%
I want to find the last one with value! Is there a way to find that value if there are more than, for ex: 5 empty cells?

**sweep** · 12-15-2011, 07:56 AM

Yes there is

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

**virtualdark** · 12-15-2011, 08:11 AM

You are absolutely correct SWEEP. Thank you :-)
I was reverting the cells when adapting your formula and obtaining strange numbers :-)

It's working has you told. Thank you

**arthurbr** · 12-15-2011, 08:24 AM

I want to find the last one with value!

Both formula just do that

How to Check if exists value and return cell

LinkBack

Thread Tools

Rate This Thread

Display

How to Check if exists value and return cell

Re: How to Check if exists value and return cell

Re: How to Check if exists value and return cell

Re: How to Check if exists value and return cell

Re: How to Check if exists value and return cell

Re: How to Check if exists value and return cell

Re: How to Check if exists value and return cell

Re: How to Check if exists value and return cell

Re: How to Check if exists value and return cell

Re: How to Check if exists value and return cell

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions