Using formula output as arguments in a Vlookup

[rogihead]

Excel 2007

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
red20	redstocking	6 garden st	green	ia	Y163694	CAS

<tbody>
[TD="align: center"]9
[/TD]

[TD="align: right"]15601
[/TD]

[TD="align: right"]7/31/2013
[/TD]
[TD="align: right"]50240
[/TD]

[TD="align: right"]3
[/TD]
[TD="align: right"]0006500136
[/TD]
[TD="align: right"]$36.92
[/TD]
[TD="align: right"]0006500136
[/TD]
[TD="align: right"]-19500297.24
[/TD]

</tbody>

Ross Data​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #E0E0F0"]
[TH="width: 10"]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]F9
[/TH]
[TD="align: left"]='RAW DATA'!I9
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]G9
[/TH]
[TD="align: left"]='RAW DATA'!P9
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]H9
[/TH]
[TD="align: left"]='RAW DATA'!K9
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]I9
[/TH]
[TD="align: left"]=REPLACE('RAW DATA'!A9,1,2,"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]J9
[/TH]
[TD="align: left"]=IF('RAW DATA'!C9="CS ","CAS", "NEED VALUE")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]K9
[/TH]
[TD="align: left"]='RAW DATA'!B9
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]L9
[/TH]
[TD="align: left"]=IFERROR(VLOOKUP('RAW DATA'!N9,INSLOOKUP,5,FALSE),"NO MATCH FOUND")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]M9
[/TH]
[TD="align: left"]=IFERROR(INDEX(DAP!F$11:F$281,MATCH(REPLACE('RAW DATA'!A9,1,2,"")+0,DAP!A$11:A$281,0)),"NO PRICE LISTED")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]N9
[/TH]
[TD="align: left"]=VLOOKUP('RAW DATA'!N9,INSLOOKUP,5,FALSE)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]O9
[/TH]
[TD="align: left"]=(M9-N9)*K9
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Workbook Defined Names[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #E0E0F0"]
[TH="width: 10"]Name
[/TH]
[TH="align: left"]Refers To
[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]DAP
[/TH]
[TD="align: left"]=DAP!$A$11:$F$281
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #E0E0F0"]INSLOOKUP
[/TH]
[TD="align: left"]='Insurance to Contract'!$A$4:$E$36
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I have another issue with the Vlookup and using the input from another cell as an argument in the formula. For this one I need to have column N to do a vlookup based of the item number in column I. The issue I have is that the contract number in column L will have its own worksheet to refer to for that specific item number. So in this case I need to look for item number 50240 on sheet 0006500136 but both of those cells are generated by formulas. So the issue I have is adding this information as arguments to the formula in N. Then in the last cell O there is one final calculation that needs to be completed, but once the cells M and N are populated I believe they will be calculated. I know that I can get the value of the sheet by using ="'"&INDIRECT("L9")&"'!" but I cannot seem to put that together with the array value and get a working formula. have also tried =VLOOKUP(I9,INDIRECT("'" & L9 & "'!$B$6:$C$23"),2,0) for N but that gives me an #REF error. Probably because the value for L9 is a result of a formula. SO I have reviewed many sites and a Vlookup book I downloaded from Mr.Excel but I do not see where this is touched on.