# Pulling Information From One Worksheet Based On A Range

This is a discussion on Pulling Information From One Worksheet Based On A Range within the Excel Questions forums, part of the Question Forums category; I have this workbook where I have various worksheets that have to work off a rate grid. This rate grid ...

1. ## Pulling Information From One Worksheet Based On A Range

I have this workbook where I have various worksheets that have to work off a rate grid. This rate grid is a separate worksheet and has four companies which has the maximum number of e-mails allowed and the rate associated for that amount of e-mails (or any under). So it

Volume company1 company2 company3 company4
250,000 \$0.0200 \$0.0240 \$0.0065 \$0.0020
500,000 \$0.0150 \$0.0170 \$0.0065 \$0.0020
1,000,000 \$0.0100 \$0.0121 \$0.0065 \$0.0020
2,500,000 \$0.0080 \$0.0121 \$0.0055 \$0.0020
5,000,000 \$0.0066 \$0.0067 \$0.0035 \$0.0020
7,500,000 \$0.0058 \$0.0067 \$0.0035 \$0.0020
10,000,000 \$0.0052 \$0.0067 \$0.0035 \$0.0020
15,000,000 \$0.0044 \$0.0067 \$0.0035 \$0.0020
20,000,000 \$0.0038 \$0.0067 \$0.0035 \$0.0020
25,000,000 \$0.0034 \$0.0067 \$0.0035 \$0.0020
30,000,000 \$0.0030 \$0.0067 \$0.0035 \$0.0020
35,000,000 \$0.0024 \$0.0067 \$0.0035 \$0.0020
40,000,000 \$0.0022 \$0.0067 \$0.0035 \$0.0020

So I have 4 other worksheets for each company. I have a certain amount of e-mails in each column that we expect to send out. So I need to pull in the correct rate based on this. So let's say the following:

In my company1 worksheet I have that I will be sending out 236,000 e-mails; it should pull \$0.0200. If I showed I was sending 500,000 then it would then switch to \$0.0150 and so on. Is there an easy way to do this?

Thanks!

Bobby

2. ## Re: Pulling Information From One Worksheet Based On A Range

It sounds like you need to use the vlookup function. It goes like this:

=vlookup(value, table range, col, type)

First I would give a name to the table with the rates (insert, name, define). The range name, whilst not absolutely necessary, makes reading the function a little clearer. Lets say you call that range "rates", and lets say that the number of emails you're looking up for company 1 is in cell b2 (on the company 1 sheet), then the formula looks like this:

=vlookup(b2,rates,2,false) - the word false can be left out - so =vlookup(b2,rates,2) will do the same. False tells excel to find the lowest closest match, true tells it to match exactly. Anyhow, the function will lookup the value in B2 in the first column of the "rates" table and return the value in the 2nd column. Change that 2 to a 3 and it will get the value in the third column i.e company 2 and so on. Hope it helps.

3. ## Re: Pulling Information From One Worksheet Based On A Range

******** ******************** ************************************************************************>
 Microsoft Excel - Book22 ___Running: xl2000 : OS = Windows Windows 2000
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 G2G6H6 =

A
B
C
D
E
F
G
H
1
Volumecompany1company2company3company4*Min*
2
250,000\$0.0200*\$0.0240*\$0.0065*\$0.0020**250,000*
3
500,000\$0.0150*\$0.0170*\$0.0065*\$0.0020****
4
1,000,000\$0.0100*\$0.0121*\$0.0065*\$0.0020**236,000500,000
5
2,500,000\$0.0080*\$0.0121*\$0.0055*\$0.0020**company1company1
6
5,000,000\$0.0066*\$0.0067*\$0.0035*\$0.0020**\$0.0200*\$0.0150*
7
7,500,000\$0.0058*\$0.0067*\$0.0035*\$0.0020****
8
10,000,000\$0.0052*\$0.0067*\$0.0035*\$0.0020****
9
15,000,000\$0.0044*\$0.0067*\$0.0035*\$0.0020****
10
20,000,000\$0.0038*\$0.0067*\$0.0035*\$0.0020****
11
25,000,000\$0.0034*\$0.0067*\$0.0035*\$0.0020****
12
30,000,000\$0.0030*\$0.0067*\$0.0035*\$0.0020****
13
35,000,000\$0.0024*\$0.0067*\$0.0035*\$0.0020****
14
40,000,000\$0.0022*\$0.0067*\$0.0035*\$0.0020****
 Sheet1 *

[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Formulas...

G2:

=MIN(A2:A14)

G6, copied to G7...

=VLOOKUP(MAX(G4,\$G\$2),\$A\$2:\$E\$14,MATCH(G5,\$A\$1:\$E\$1,0),1)

4. ## Re: Pulling Information From One Worksheet Based On A Range

That almost worked. But try doing company2 for 500,001. I am getting \$0.0170 when it should be \$0.0121. Any ideas?

Thanks,
Bobby

5. ## Re: Pulling Information From One Worksheet Based On A Range

Originally Posted by robertuva
That almost worked. But try doing company2 for 500,001. I am getting \$0.0170 when it should be \$0.0121. Any ideas?

Thanks,
Bobby
Specs are all important...

******** ******************** ************************************************************************>
 Microsoft Excel - Book22 ___Running: xl2000 : OS = Windows Windows 2000
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 G2G6H6 =

A
B
C
D
E
F
G
H
1
Volumecompany1company2company3company4*Min*
2
250,000\$0.0200*\$0.0240*\$0.0065*\$0.0020**250,000*
3
500,000\$0.0150*\$0.0170*\$0.0065*\$0.0020****
4
1,000,000\$0.0100*\$0.0121*\$0.0065*\$0.0020**236,000500,001
5
2,500,000\$0.0080*\$0.0121*\$0.0055*\$0.0020**company1company2
6
5,000,000\$0.0066*\$0.0067*\$0.0035*\$0.0020**\$0.0200*\$0.0121*
7
7,500,000\$0.0058*\$0.0067*\$0.0035*\$0.0020****
8
10,000,000\$0.0052*\$0.0067*\$0.0035*\$0.0020****
9
15,000,000\$0.0044*\$0.0067*\$0.0035*\$0.0020****
10
20,000,000\$0.0038*\$0.0067*\$0.0035*\$0.0020****
11
25,000,000\$0.0034*\$0.0067*\$0.0035*\$0.0020****
12
30,000,000\$0.0030*\$0.0067*\$0.0035*\$0.0020****
13
35,000,000\$0.0024*\$0.0067*\$0.0035*\$0.0020****
14
40,000,000\$0.0022*\$0.0067*\$0.0035*\$0.0020****
 Sheet1 *

[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

G2:

=MIN(A2:A14)

G6, which is copied to G7...

=INDEX(\$B\$2:\$E\$14,MATCH(MAX(G4,G2),\$A\$2:\$A\$14)+(VLOOKUP(MAX(G4,G2),\$A\$2:\$A\$14,1)<>MAX(G4,G2)),MATCH(G5,\$B\$1:\$E\$1,0))

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•