Budget: VLookup & Match Index

[lmmurphy]

In my budget that goes out a couple of thousand rows, I am trying to pin a table to the top that will find and return 1. the lowest number and the respective date so I always know my low point and 2. a list of all negative numbers and their respective dates. The calculations change as I plug my income and expenses in from month to month so I'd like the table to stay up to date for me without a lengthy scroll. For clarity this is just a simple example of how the data is laid out on my actual budget. Right now I manually search and refer to the low point cell or negative cells after I find them. I believe I need to use a V or X lookup for the amounts and a match index for the dates but I am unsure how to achieve this or if I'm on the right track. Please oh please you beautiful EXCELlent people, help a girl out.

 

[DanteAmor]

Hi @lmmurphy, Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

I propose the following array formulas.
You must copy the formulas in columns I:J downwards the number of times you need, if you want to see 10 negative numbers, then you copy it 10 times, if you want to see 100 negative numbers, then you copy it 100 times.

Dante Amor
	A	B	C	D	E	F	G	H	I	J
1						The lowest number	Date		List of all negative numbers	Date
2		Date:	01/01/2023			-300	12/02/2023		-100	15/01/2023
3		Amount:	1000						-300	12/02/2023
4
5
6
7		Date:	15/01/2023
8		Amount:	-100
9
10
11
12		Date:	29/01/2023
13		Amount:	800
14
15
16
17		Date:	12/02/2023
18		Amount:	-300
19
20
21
22		Date:	26/02/2023
23		Amount:	600
Hoja1

Cell Formulas
Range	Formula
F2	F2	=MIN((B1:B30000="Amount:")*(C1:C30000))
G2	G2	=INDEX(C:C,MATCH(F2,C:C,0)-1)
I2:I11	I2	=IFERROR(INDEX($C$1:$C$30000,SMALL(IF($C$1:$C$30000<0,ROW($C$1:$C$30000)),ROW()-1)),"")
J2:J11	J2	=IF(I2="","",INDEX(C:C,MATCH(I2,C:C,0)-1))
Press CTRL+SHIFT+ENTER to enter array formulas.

I suppose that for some reason you have that structure of your data in your sheet, but it will be simpler to manage your data if you had it in a list, for example:

Dante Amor
	A	B	C	D
1		Date	Amount
2		01/01/2023	1000
3		15/01/2023	-100
4		29/01/2023	800
5		12/02/2023	-300
6		26/02/2023	600
7
Hoja2

This way you could use a pivot table and get the data you need.


----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[lmmurphy]

Hello and thank you SO much Dante. The 2nd table works perfectly and is a huge help. I'm a bit lost on the first 'lowest number one though as I'm being returned a 0 and there are no 0s. Do you know what I might be doing wrong?

Re: the layout - yes, fatal error that I realized far too late in the game and now I'm just committed to it. Sorry it is such a pain in the you know what

Hi @lmmurphy, Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

I propose the following array formulas.
You must copy the formulas in columns I:J downwards the number of times you need, if you want to see 10 negative numbers, then you copy it 10 times, if you want to see 100 negative numbers, then you copy it 100 times.

Dante Amor
	A	B	C	D	E	F	G	H	I	J
1						The lowest number	Date		List of all negative numbers	Date
2		Date:	01/01/2023			-300	12/02/2023		-100	15/01/2023
3		Amount:	1000						-300	12/02/2023
4
5
6
7		Date:	15/01/2023
8		Amount:	-100
9
10
11
12		Date:	29/01/2023
13		Amount:	800
14
15
16
17		Date:	12/02/2023
18		Amount:	-300
19
20
21
22		Date:	26/02/2023
23		Amount:	600
Hoja1

Cell Formulas
Range	Formula
F2	F2	=MIN((B1:B30000="Amount:")*(C1:C30000))
G2	G2	=INDEX(C:C,MATCH(F2,C:C,0)-1)
I2:I11	I2	=IFERROR(INDEX($C$1:$C$30000,SMALL(IF($C$1:$C$30000<0,ROW($C$1:$C$30000)),ROW()-1)),"")
J2:J11	J2	=IF(I2="","",INDEX(C:C,MATCH(I2,C:C,0)-1))
Press CTRL+SHIFT+ENTER to enter array formulas.

I suppose that for some reason you have that structure of your data in your sheet, but it will be simpler to manage your data if you had it in a list, for example:

Dante Amor
	A	B	C	D
1		Date	Amount
2		01/01/2023	1000
3		15/01/2023	-100
4		29/01/2023	800
5		12/02/2023	-300
6		26/02/2023	600
7
Hoja2

This way you could use a pivot table and get the data you need.


----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[DanteAmor]

I'm a bit lost on the first 'lowest number

=MIN((B1:B30000="Amount:")*(C1:C30000))

That is an array formula, to enter it, you must edit it and press the 3 keys Shift + Control + Enter (Not just Enter)
If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself.

Also check that all the "Amount" labels in your sheet must have a value in the adjacent cell, if any label has a blank in the adjacent cell, then it returns a 0.


-------
Reconsider changing the layout, otherwise you will continue to have a headache...

 

[lmmurphy]

=MIN((B1:B30000="Amount:")*(C1:C30000))

That is an array formula, to enter it, you must edit it and press the 3 keys Shift + Control + Enter (Not just Enter)
If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself.

Also check that all the "Amount" labels in your sheet must have a value in the adjacent cell, if any label has a blank in the adjacent cell, then it returns a 0.


-------
Reconsider changing the layout, otherwise you will continue to have a headache...

Thanks Dante! I will definitely fix my format next time I rewrite it. I can't thank you enough for your help.

 

[DanteAmor]

Im glad to help you, thanks for the feedback