help with lookup formula (sheet 2, not a double post)

[chocolab61571]

I need to make a formula using a lookup. The first spreadsheet is a
inventory (ALO INVENTORY JP). The second (P&G 103) is a chart tha
tells number of gallons of alcohol per foot/inch/fraction.
On ALO INVENTORY JP I insert the # of feet, inches, and the fraction i
cells R5 and T5. I want these numbers to lookup the number of gallon
off of the P&G 103 worksheet.
For example: P&G 103 (on inventory sheet) is 26ft 4 1/2 inches, i
should look-up to be 34572 gal. for the 26'4" and 55 gal. for the 1/2"
These two numbers should be added together and multiplied by the numbe
in cell N5. The result should go in cell V5

Attachment filename: p&g 103.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=50333

 

[Frank Kabel]

Hi
first a remark: Most people won't open attachments - so you limit your
responses. Try explaining your issue in plain text and also post sample
data in plain text.

For your problem: first a question: Isn't there a formula which could
calculate the gallons based on your dimensions. This would be much
easier

If you have to use a lookup table try the following:
1. change the layout of your p&g103.xls list:
- column a has to have a feet value for all rows (not just for the
first row). So you have to copy the values and fill all blank rows
- also you have to put a zero inch value in the currently blank
rows in column B

2. After this use the following array formula (entered with
CTRL+SHIFT+ENTER). Note: only works with the changes in point 1.
=INDEX('[p&g 103.xls]p&g103'!$C$3:$C$500,MATCH(R5&INT(T5),'[p&g
103.xls]p&g103'!$A$3:$A$500&'[p&g
103.xls]p&g103'!$B$3:$B$500,0))+VLOOKUP(MOD(T5,1),'[p&g
103.xls]p&g103'!$I$3:$J$18,2,0)

I also assumed that both sheets are open (so I don't add a path name)