IF Function HelpVLOOKUP

**aero613** · 03-10-2010, 06:21 PM

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

**DonkeyOte** · 03-11-2010, 04:36 AM

Your narrative does not really tie out with your formula...

I suspect you're saying:

a) MySheets named range consists of 11 cells - each cell containing the name of one of the 11 sheets of interest.

b) B2:B11 contains the corresponding table that should be used (3C+ for 1st sheet, 4C+ for second sheet etc)
What we don't know - as yet - is what B2:B11 contains specifically - does this contain a range reference, a named range perhaps ?

c) You wish to Sum all values resulting from b)

Is that correct ?

3D Conditional Calculations are (at best) complex and inefficient

Given the above I would suggest you simply calculate your lookup results based on C2 adjacent to B2:B11 - ie one calculation per sheet and simply sum the 11 results.

I suspect you may be conducting multiple calculations ? (ie numerous "C2" values)
If that is indeed the case then avoiding the 3D calculation is even more worthwhile given they will be expensive (& volatile).

Just my opinion of course - disregard as you see fit.

**pomidor** · 03-13-2010, 03:33 PM

Hi,
Could somebody explain me why my formula works with negative values and not with positive?
=IF(ABS(VLOOKUP(E24,P108:T113,5,FALSE))>S109,U18,IF(ABS(VLOOKUP(E24,P108:T113,5,FALSE))>R109,U17,IF( ABS(VLOOKUP(E24,P108:T113,5,FALSE))>Q109,U16,U15)))