Vlookup formula

[Amnon Wilensky]

Hi,



I need a nested formula like vlookup formula.



This is my problem:



I have 8 size of drainage pipes differ by diameter, and every size have
three grades of prices as shown in the table below:



Diameter type A type B type C



40 $50 $70 $90

50 $60 $85 $105

80 $85 $110 $135

100 $125 $165 $195

125 $195 $240 $310

150 $310 $425 $580

180 $550 $720 $950

225 $835 $1150 $1650



Now I have a formula that calculate all the cost of performing new system of
drainage pipes (excavating, materials, labor, est., and I need a nested
formula to bring in the price of the correct pipe, for example, if I will
type in one cell 100 and the other B, the formula will bring to me the value
$165 according to the table above.



Any help?



Thanks,

Amnon




--


---------------------------------------------------------------------
"Are you still wasting your time with spam?...
There is a solution!"

Protected by GIANT Company's Spam Inspector
The most powerful anti-spam software available.
http://mail.spaminspector.com

 

[Alan]

With your table of values in A19
The diameter cell being A12,
The type cell being A13,

=VLOOKUP(A12,A29,MATCH(B12,A11,FALSE))

Regards,

 

[JE McGimpsey]

One way:

=VLOOKUP(cell1,A19,MATCH(cell2,{"A","B","C"},FALSE)+1,FALSE)

 

[Alan]

Sorry, that should be
=VLOOKUP(A12,A29,MATCH(B12,A11),FALSE)
Regards,

 

[Alan]

Not my day!
The type cell should be B12,
Regards.

Sorry, that should be
=VLOOKUP(A12,A29,MATCH(B12,A11),FALSE)
Regards,