VLOOKUP using multiple worksheets

cubs · Aug 11, 2011

I’m trying to use one of two worksheets in my workbook to do a vlookup based on the contents of a specific cell. In my worksheet I have a cell that using data validation can either be 3% or 7% If the cell has 3% in it I would like to use the worksheet called "3% Support" to do a vlookup for another cell and of course if the cell says 7% then I would like to use the 7% support worksheet. Thank you.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

></o

>

MrVillareal · Aug 11, 2011

Hi!
Use IF Function for the table array as you see below.

3 sheets: 3%Support, 7%Support, and Sheet 3

In sheet 3,

C2=VLOOKUP(A2,IF(B2=3%,'3%Support'!$A$1:$B$6,'7%Support'!$A$1:$B$6),2)

where;

A2 is the lookupValue
B2 is the 3% or 7%

then, the table array and columns index.

Hope this help you.

MrVillareal · Aug 12, 2011

Excel Workbook

A

B

C

1

LookupValue

LookupTable

ReturnValue

2

100

3%

1%

3

100

7%

2%

Sheet3

Excel 2010

Cell Formulas
Range	Formula
C2	=VLOOKUP(A2,IF(B2=3%,'3%Support'!$A$1:$B$6,'7%Support'!$A$1:$B$6),2)
C3	=VLOOKUP(A3,IF(B3=3%,'3%Support'!$A$1:$B$6,'7%Support'!$A$1:$B$6),2)

Excel Workbook

A

B

1

0

0%

2

100

1%

3

300

3%

4

500

5%

5

700

7%

6

900

9%

3%Support

Excel 2010

Excel Workbook

A

B

1

0

0%

2

100

2%

3

300

4%

4

500

6%

5

700

8%

6

900

10%

7%Support

Excel 2010

cubs · Aug 12, 2011

[FONT=&quot]A1= Current number of licenses.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]A2= Current annual support amount.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]A3= Annual Renewal %. Data validation only allows 3%, 7%, or 10% to be[/FONT]
[FONT=&quot] entered.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]A4=New total number of licenses. This will be different each time. [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]A5= New support amount based on the new number of licenses.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Based on what value is in 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<sup>nd</sup> column of each of the 3 worksheets. The 1<sup>st</sup> column contains the number of licenses. [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Basic Example Below[/FONT]

[FONT=&quot]A1 = 50 [/FONT]
[FONT=&quot]A2 = $200.00[/FONT]
[FONT=&quot]A3= 3% | 7% | 10%[/FONT]
[FONT=&quot]A4= 100[/FONT]
[FONT=&quot]A5= $250.00 Auto populates A5 with the new support amt. for 100 licenses [/FONT]
[FONT=&quot] from the 3% matrix worksheet. If 7% was chosen in A3 it [/FONT]
[FONT=&quot] would have populated A5 with $251.00 and the 10%[/FONT]
[FONT=&quot] worksheet would have = $269.00[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]3% Support[/FONT][FONT=&quot] [/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] [/FONT]
[FONT=&quot]7% Support[/FONT][FONT=&quot] worksheet A = Number of licenses B=New support amount[/FONT]
[FONT=&quot] [/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] [/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] [/FONT]
[FONT=&quot]Thank you again for your help.[/FONT]

MrVillareal · Aug 12, 2011

Wow.

ok how about this.

Excel Workbook

A

1

50

2

200

3

10%

4

100

5

269

Support

Excel 2010

Cell Formulas
Range	Formula
A5	=VLOOKUP(A4,IF(A3=3%,'3%Support'!A1:B3,IF(A3=7%,'7%Support'!A1:B3,IF(A3=10%,'10%Support'!A1:B3,0))),2)

Excel Workbook

A

B

1

99

242.00

2

100

250.00

3

101

252.50

3%Support

Excel 2010

Excel Workbook

A

B

1

99

253.00

2

100

251.00

3

101

255.75

7%Support

Excel 2010

Excel Workbook

A

B

1

99

261.00

2

100

269.00

3

101

272.25

10%Support

Excel 2010

Marcelo Branco · Aug 12, 2011

Duplicate post

http://www.mrexcel.com/forum/showthread.php?t=571264

cubs · Aug 12, 2011

MrVillareal this worked perfectly! Thank you so much for your help and your follow up solutions.

MrVillareal · Aug 12, 2011

Welcome! Glad I help

VLOOKUP using multiple worksheets

cubs

New Member

Excel Facts

MrVillareal

Well-known Member

MrVillareal

Well-known Member

cubs

New Member

MrVillareal

Well-known Member

Marcelo Branco

MrExcel MVP

cubs

New Member

MrVillareal

Well-known Member

Similar threads

Forum statistics

Share this page

VLOOKUP using multiple worksheets

New Member

Excel Facts

Well-known Member

Well-known Member

New Member

Well-known Member

MrExcel MVP

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock