vlookup if 1st condition is met

[chickey]

I am trying to get the next job number using vlookup once the first requirement is met.

I am using an If and vlookup statemnt

if((qty*-1)< net, vlookup customer part number,customer part number:Job number,2,false),0)

once this requirement is met i want it to find the next job number.</SPAN>

Customer Part Number	qty	NET	Job Number			Part Number	Job Number
S50372-000	16	-4	197203-001			S50372-003	197126-001
S50372-000	16	-9	197203-001			S50372-000	197203-001
S50372-000	16	-11	197203-001			S50433-001	197348-001
S50372-000	16	-14	197203-001			S50433-002	197349-001
S50372-000	16	-21	197516-001			S50433-000	197508-001
S50372-000	16	-26	197516-001			S50372-000	197516-001
S50372-000	16	-31	197516-001			S50433-001	197596-001
S50372-000	16	-52	197516-001			S50433-002	197609-001
S50372-000	16	-95	197516-001			S50372-000	197688-001
S50372-000	16	-31	197516-001			S50421-004	197722-001
S50372-000	16	-31	197516-001			S50433-001	197771-001
S50372-000	16	-31	197516-001
S50372-000	16	-31	197516-001
S50372-000	16	-31	197516-001

<TBODY>
</TBODY>

 

[LloydFinancials]

Hi Chickey,

Give this a try. I changed up your data a little bit for testing:

Excel 2010
	A	B	C	D	E	F	G
1	Customer Part Number	qty	NET	Job Number		Part Number	Job Number
2	S50372-000	16	-4	197203-001		S50372-003	197126-001
3	S50372-000	16	-9	197516-001		S50372-000	197203-001
4	S50372-000	16	-11	197688-001		S50433-001	197348-001
5	S50372-000	16	-14			S50433-002	197349-001
6	S50372-000	16	-21			S50433-000	197508-001
7	S50433-002	16	-5	197349-001		S50372-000	197516-001
8	S50433-002	16	4	197609-001		S50433-001	197596-001
9	S50372-000	16	-52			S50433-002	197609-001
10	S50372-000	16	-95			S50372-000	197688-001
11	S50372-000	16	-31			S50421-004	197722-001
12	S50372-000	16	-31			S50433-001	197771-001
13	S50372-000	16	-31
14	S50372-000	16	-31
15	S50421-004	16	5	197722-001
Sheet1

Cell Formulas
Range	Formula
D2	{=IFERROR(INDEX($G$2:$G$12,SMALL(IF($F$2:$F$12=A2,ROW($G$2:$G$12)-ROW($G$2)+1),IF(AND($A2<>$A1,(B2*-1)),1,IF(AND((B2*-1)),ROW($A2)+1-MATCH($A2,$A$1:$A$15,0),""))),1),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Best,

Luke

 

[chickey]

Luke,

Thanks for the help. I will give it a try.

Thanks again

 

[chickey]

good Morning Luke,

Thanks for the formula, I just have one question. what do i need to chnge? Based on the results I should not be gettin the next job number until "c" is greater than "b*-1" on the first part nuber i should not get a different result until line 6. thanks in advance for your help.

Craig

 

[LloydFinancials]

Hi, thanks for the great question.

I think you are saying that there should be a result in line 5. There isn't because there are only 3 instances of S50372-000 in Column F. If Line 5 should have a value, what would it be?

 

[jasonb75]

Craig,

Based on your samples and information so far

=IFERROR(INDEX($H$2:$H$12,SMALL(IF($G$2:$G$12=A2,(ROW($G$2:$G$12)-ROW($G$2))+1),IF(B2>ABS(C2),1,2))),"")

Array confirmed with Shift Ctrl Enter.

Note that this does only cover the first 2 job numbers in the list as you asked, at what point, if any, should the result move to the 3rd job number, then 4th, etc?