Hi,
I'm trying to add to the following formula, and IF Function of the example below.
=VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&MySheets&"'!$A$2:$A$31"),A2)>0,0 ))&"'!$A$4:$C$31"),3,FALSE)
Eg: cell C2 is my quantity. I would like to use the formula above which vlookup each worksheet and add to it, a formula that look up the price corresponding to the quantity in cell C2 using B2 or B11 as a fixed variable.
Eg: if C2 is 100,00. I need a formula to search through 11 worksheets, match cell B2 and use the price in column B corresponding to the quantity in cell C2.
A B C
1 3C + LAM Price/m Extended Price
2 5,000 $131.49 $657.45
3 25,000 $34.32 $858.00
4 50,000 $21.90 $1,095.00
5 100,000 $15.39 $1,539.00
6 200,000 $12.15 $2,430.00
7 250,000 $10.92 $2,730.00
8 300,000 $10.58 $3,174.00
9
10 4C + LAM Price/m Extended Price
11 10,000 $82.84 $828.40
12 25,000 $38.50 $962.50
13 50,000 $23.87 $1,193.50
14 100,000 $16.47 $1,647.00
15 200,000 $12.65 $2,530.00
16 250,000 $11.44 $2,860.00
17 300,000 $11.06 $3,318.00
Bookmarks