Finding a number

[damian_r_Home]

Evening All.
I need help with two formulas that lookup dates and return a value from an adjacent cell.

The first of these formulas is as follows.

In range A5 to A1500 there will be a range on ascending dates.
In column C5 to C1500 there will occasionally be a numerical value.

I need a formula that looks down the date range and then returns the value it finds in the same row but over in column C.
The problem is that i can't just use a formula that finds the highest date value as that may not have a corresponding value over in row C... it could be the last time a date has a number over in column C is 10, 100, or 200 rows up from the last date in the A5:A1500 range.

The second formula (I hope is easier) is that in the range C5:C1500 i want a formula which just give the first number it finds in that cell working from top to bottom.

Hope you can help.

D

 

[FDibbins]

For the 1st, I would use vlookup(), something like...
=vlookup(search-criteria,$A$5:$A$1500,3,0)

For the 2nd...
=INDEX($C$5$C$1500,MATCH(TRUE,INDEX(($C5:$C1500<>0),0),0))

 

[Aladin Akyurek]

@damian_r_Home

It's not clear what you are after with the first formula you sketch. Are you looking for the date which has a corresponding numeric value in column C?

 

[damian_r_Home]

Hi Aladin
It was an awkward thing to try and describe...I'll try here again in a representation of a short version of the sheet.

Col A Col B Col C
5th May
6th May 1007
7th May
8th May 987
9th May
10th May
11th May 995
12th May
13th May

OK, i'm looking for a formula that looks will show me the most recent value that appears in Col C when working from the top to the bottom of the list...
Column B will have data in almost every cell as well but that bears no relevance to the Col A or Col C values.

Hope this helps?

D

 

[damian_r_Home]

Afternoon FDibbins

I've not tried the first formula but the second worked a treat.
Is there a version of the second one that would give the last number it finds in a column working top to bottom?

D

 

[Aladin Akyurek]

Hi Aladin
It was an awkward thing to try and describe...I'll try here again in a representation of a short version of the sheet.

Col A Col B Col C
5th May
6th May 1007
7th May
8th May 987
9th May
10th May
11th May 995
12th May
13th May

OK, i'm looking for a formula that looks will show me the most recent value that appears in Col C when working from the top to the bottom of the list...
Column B will have data in almost every cell as well but that bears no relevance to the Col A or Col C values.

Hope this helps?

D

What is the desired value here?

 

[FDibbins]

Maybe this?
=INDEX($C$1:$C$1,MATCH(0,C2:C2,-1))