Return the value of a vlookup only if negative.

[albertocantupomares]

I was wondering if someone would help me with the following situation,

On another sheet I used Vlookup to get the "Request date" based on the part number, in this case if I use =Vlookup,(1.0617874,A3:J5,7,False) I get "5/31/2021", but I wanted to know how can I get to return the request date, base on the balance, in this case the first "Request Date" value with negative "Balance" (J column). In this example I need the value from G5 because the balance on the same row is the first one with negative number (-31,300).

Also I would appreciate if you could let me know how to ask this kind of question properly, I not sure if I asked this correctly or used more than needed information.

I appreciate the support

 

[Eric W]

Welcome to the MrExcel forum!

First, you posed your question just fine. However, I would recommend downloading and using the XL2BB add-in. See the link in my signature or the reply box. It makes it much easier for the helpers here to copy and test with your data without having to retype everything. It's easy to download, install, and use.

Next, see if this works for you:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2	Part Number						Request Date			Balance			Part Number	Date for negative balance
3	1.061787						5/31/2021			12300			1.061787	6/7/2021
4	1.061787						5/31/2021			700
5	1.061787						6/7/2021			-31300
6	1.061787						6/14/2021			-63300
Sheet15

Cell Formulas
Range	Formula
N3	N3	=INDEX(Sheet15!G3:G6,AGGREGATE(15,6,(ROW(Sheet15!G3:G6)-ROW(Sheet15!G3)+1)/(Sheet15!A3:A6=M3)/(Sheet15!J3:J6<0),1))

 

[kweaver]

Another approach:

Book3
	A	B	C	D	E	F	G	H	I	J	K	L
2	P:art #	Rev	Desc	Order #	PO #	SO	Req Date	Orig	Left	Bal		1.0617874
3	1.0617874						5/31/2021			12,300		6/7/2021
4	1.0617874						5/21/2021			700
5	1.0617874						6/7/2021			-31,300
6	1.0617874						6/14/2021			-63,300
7	2.12345						7/10/2021			-20,000
8	2.0618888						6/21/2021			-31,300
Sheet1

Cell Formulas
Range	Formula
L3	L3	=INDEX(G3:G12,MATCH(MAX(IF(((L2=A3:A12)*J3:J12)<0,J3:J12)),J3:J12,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[albertocantupomares]

Welcome to the MrExcel forum!

First, you posed your question just fine. However, I would recommend downloading and using the XL2BB add-in. See the link in my signature or the reply box. It makes it much easier for the helpers here to copy and test with your data without having to retype everything. It's easy to download, install, and use.

Thanks a lot, this worked almost great, the only thing that I notice is that the sometimes the part number is not entirely as number it may contain some letter in it. I was wondering how to do the proper fix for this?? should I add the little " ' " at the first of the part number on M3?

 

[Eric W]

It shouldn't matter if the part number is numeric or not, the formula should find it, as long as it is saved the same way. Is column A formatted as text? If so, format M3 as text. I wondered about the part number format, since if you entered it as 1.067845, then Excel would interpret as a number, and if it was long enough, you'd have problems with Excel's 15 digit limit. Unless you formatted the cells as text. So try formatting everything the same and see what happens. If it doesn't work, see if you can provide an example.

 

[albertocantupomares]

I appreciate the support, after some adjustments it worked, definitely need to work and learn some of the formulas you share.
Thanks.

 

[Eric W]

Lots of practice! Glad we could help!