Conditional Vlookup

[arlene_fool]

I am trying to create a report driven by a large table, for easy reporting to my salesmen.
Below is a section of the large table, and then the section of the report i am having troubles with, on the table i was abe to create conditions so it would show if the shortages listed were a neg number it would just format the color not to show. how do i do a vlookup, where if the number returned is a negative number it just puts in 0?

Excel Workbook
	A	J	K	L	M	N	O
4	#	21'	20'	19'	18'	17'	16'
26	22	-95	1	-36	16	1	70
27	23	-95	40	-36	126	0	7
Shortages

Excel 2010

Cell Formulas
Range	Formula
A4	='Orders Sheet'!A4
A26	='Orders Sheet'!A26
A27	='Orders Sheet'!A27
J4	='Orders Sheet'!J4
J26	=IF((SUM('Orders Sheet'!J$5:J26))-J$51>'Orders Sheet'!J26,'Orders Sheet'!J26,(SUM('Orders Sheet'!J$5:J26))-J$51)
J27	=IF((SUM('Orders Sheet'!J$5:J27))-J$51>'Orders Sheet'!J27,'Orders Sheet'!J27,(SUM('Orders Sheet'!J$5:J27))-J$51)
K4	='Orders Sheet'!K4
K26	=IF((SUM('Orders Sheet'!K$5:K26))-K$51>'Orders Sheet'!K26,'Orders Sheet'!K26,(SUM('Orders Sheet'!K$5:K26))-K$51)
K27	=IF((SUM('Orders Sheet'!K$5:K27))-K$51>'Orders Sheet'!K27,'Orders Sheet'!K27,(SUM('Orders Sheet'!K$5:K27))-K$51)
L4	='Orders Sheet'!L4
L26	=IF((SUM('Orders Sheet'!L$5:L26))-L$51>'Orders Sheet'!L26,'Orders Sheet'!L26,(SUM('Orders Sheet'!L$5:L26))-L$51)
L27	=IF((SUM('Orders Sheet'!L$5:L27))-L$51>'Orders Sheet'!L27,'Orders Sheet'!L27,(SUM('Orders Sheet'!L$5:L27))-L$51)
M4	='Orders Sheet'!M4
M26	=IF((SUM('Orders Sheet'!M$5:M26))-M$51>'Orders Sheet'!M26,'Orders Sheet'!M26,(SUM('Orders Sheet'!M$5:M26))-M$51)
M27	=IF((SUM('Orders Sheet'!M$5:M27))-M$51>'Orders Sheet'!M27,'Orders Sheet'!M27,(SUM('Orders Sheet'!M$5:M27))-M$51)
N4	='Orders Sheet'!N4
N26	=IF((SUM('Orders Sheet'!N$5:N26))-N$51>'Orders Sheet'!N26,'Orders Sheet'!N26,(SUM('Orders Sheet'!N$5:N26))-N$51)
N27	=IF((SUM('Orders Sheet'!N$5:N27))-N$51>'Orders Sheet'!N27,'Orders Sheet'!N27,(SUM('Orders Sheet'!N$5:N27))-N$51)
O4	='Orders Sheet'!O4
O26	=IF((SUM('Orders Sheet'!O$5:O26))-O$51>'Orders Sheet'!O26,'Orders Sheet'!O26,(SUM('Orders Sheet'!O$5:O26))-O$51)
O27	=IF((SUM('Orders Sheet'!O$5:O27))-O$51>'Orders Sheet'!O27,'Orders Sheet'!O27,(SUM('Orders Sheet'!O$5:O27))-O$51)

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Line #	22
12			Ordered	Available	Short			Ordered	Available	Short
13	Aluma Beam 6-1/2"					Aluma Frames
14	109	4' Beam	8	-99	-107	245	Aluma Frame 6x8		0
15	111	5' Beam	4	696	-692	215	Aluma Frame 6x6		0
Report

Excel 2010

Cell Formulas
Range	Formula
C14	=VLOOKUP(Report!$B$1,'Orders Sheet'!$A$5:$BZ$49,28,FALSE)
C15	=VLOOKUP(Report!$B$1,'Orders Sheet'!$A$5:$BZ$49,27,FALSE)
D14	=E14+C14
D15	=C15-E15
E14	=VLOOKUP(Report!$B$1,Shortages!$A$5:$BZ$49,28,FALSE)
E15	=VLOOKUP(Report!$B$1,Shortages!$A$5:$BZ$49,27,FALSE)
I14	=H14-J14
I15	=H15-J15

 

[arlene_fool]

nevermind, i figured it out as soon as i hit the submit button

=IF((VLOOKUP(Report!$B$1,Shortages!$A$5:$BZ$49,28,FALSE))<0,0,VLOOKUP(Report!$B$1,Shortages!$A$5:$BZ$49,28,FALSE))

 

[Aladin Akyurek]

nevermind, i figured it out as soon as i hit the submit button

=IF((VLOOKUP(Report!$B$1,Shortages!$A$5:$BZ$49,28,FALSE))<0,0,VLOOKUP(Report!$B$1,Shortages!$A$5:$BZ$49,28,FALSE))

Or...

=MAX(0,VLOOKUP(Report!$B$1,Shortages!$A$5:$BZ$49,28,0))