VLOOKUP using one of three worksheets based on a data validation cell with 3 choices

[cubs]

[FONT=&quot]Based on what's in cell A3 (3%, 7%, or 10%) I want it to choose one of my three corresponding support matrix workbooks; “3%SUPPORT”, “7%SUPPORT”, “10%SUPPORT” and then using VLOOKUP cross reference the total number of new licenses in A4 and give me the new support amount in A5 which is in the 2^nd column of each of the 3 worksheets. The 1^st column contains the number of licenses. [/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]A1= Current number of licenses.[/FONT]

[FONT=&quot]A2= Current annual support amount.[/FONT]

[FONT=&quot]A3= Annual Renewal %. Data validation only allows 3%, 7%, or 10% to be[/FONT]
[FONT=&quot] entered.[/FONT]

[FONT=&quot]A4=New total number of licenses. This will be different each time. [/FONT]

[FONT=&quot]A5= New support amount based on the new number of licenses.[/FONT]

[FONT=&quot]
[/FONT]

[FONT=&quot]Basic Example Below[/FONT]

[FONT=&quot]A1 = 50 (Current number of licenses)
[/FONT]
[FONT=&quot]A2 = $200.00 (Current annual support amount)
[/FONT]
[FONT=&quot]A3= 3% | 7% | 10% (This client is using the 3% support matrix)
[/FONT]
[FONT=&quot]A4= 100 (New number of licenses)
[/FONT]
[FONT=&quot]A5= $250.00 Auto populates A5 with the new support amount for 100[/FONT]
[FONT=&quot] licenses from the 3% matrix worksheet. If 7% was chosen in
[/FONT]
[FONT=&quot] A3 it would have populated A5 with $251.00 and the 10%[/FONT]
[FONT=&quot] worksheet would have = $269.00)[/FONT]

[FONT=&quot]3% Support[/FONT][FONT=&quot]worksheet; A = Number of licenses B=New support amount[/FONT]
[FONT=&quot]A B[/FONT]
[FONT=&quot]99 $242.00[/FONT]
[FONT=&quot]100 [/FONT][FONT=&quot]$250.00[/FONT]
[FONT=&quot]101 $252.50[/FONT]

[FONT=&quot]7% Support[/FONT][FONT=&quot] worksheet A = Number of licenses B=New support amount[/FONT]

[FONT=&quot]A B[/FONT]
[FONT=&quot]99 $253.00[/FONT]
[FONT=&quot]100 $251.00[/FONT]
[FONT=&quot]101 $255.75[/FONT]

[FONT=&quot]10% Support[/FONT][FONT=&quot] worksheet A= Number of licenses B=New support amount[/FONT]
[FONT=&quot]A B[/FONT]
[FONT=&quot]99 $261.00[/FONT]
[FONT=&quot]100 $269.00[/FONT]
[FONT=&quot]101 $272.25[/FONT]

[FONT=&quot]Thank you for your help.[/FONT]

 

[jbeaucaire]

The MATCH() function lets you get a numeric position 1,2,3 of a selected value. So on your A3 value of 3%, this formula would result in 1:

=MATCH(A3, {0.03, 0.07, 0.1}, 0)


The CHOOSE() function lets you select from a series of formulas based on the numeric result of the first parameter:

=CHOOSE(NumericFormulaCalc, 1stFormula, 2ndFormula, 3rdFormula, etc...)

In your case so far it would be:

=CHOOSE(MATCH(A3, {0.03, 0.07, 0.1}, 0), VLOOKUP(A4, '[3%Support.xls]Sheet1!'$A$B, 2, 0), 2ndFormula, 3rdFormula, etc...)


Now you just need to insert your 3 different workbook reference VLOOKUP() formulas in the choose slots. The example above is a pure guess, you just insert your own known working VLOOKUP formulas and the CHOOSE function will pick the correct one.