vlookup- sumif

bonovox1

Board Regular
Joined
Aug 11, 2007
Messages
91
hi, i have 2 columns in cols A and B ( a date column and an amount Column.) I want to enter a value in cell C1, say 5000, and my formula will return the date where this value is attained. i.e on below example 31/07/2015. Appreciate if somebody could help me on this.

31/10/2014 500
30/01/2015 1000
30/04/2015 1000
20/07/2015 1000
21/07/2015 1000
31/07/2015 1000
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

davesweep

Well-known Member
Joined
Apr 30, 2007
Messages
510
Hello,

in E1, and copied down:=SUM($B$1:B1)

=INDEX(A1:A6,MATCH(C1,E1:E6))
 

BobUmlas

Well-known Member
Joined
Mar 14, 2002
Messages
1,170
You'll need a helper column (or VBA). In D1, enter =SUM($B$1:B1) and fill down. Then in E1:
=INDEX(A:A,IF(ISNA(MATCH(C1,D:D,0)),MATCH(C1,D1:D100,1)+1,MATCH(C1,D1:D100,0)))
 

BobUmlas

Well-known Member
Joined
Mar 14, 2002
Messages
1,170
Doesn't work. Try numbers like:
100
200
1000
2000
1000
2000
and put 4301 in C1.



<colgroup><col style="width: 48pt;" width="64">
<tbody>


</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,108,490
Messages
5,523,251
Members
409,506
Latest member
reneekeane

This Week's Hot Topics

Top