Pulling Information From One Worksheet Based On A Range

robertuva

New Member
Joined
Mar 10, 2003
Messages
30
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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.
 
Upvote 0
Book22
ABCDEFGH
1Volumecompany1company2company3company4Min
2250,000$0.0200$0.0240$0.0065$0.0020250,000
3500,000$0.0150$0.0170$0.0065$0.0020
41,000,000$0.0100$0.0121$0.0065$0.0020236,000500,000
52,500,000$0.0080$0.0121$0.0055$0.0020company1company1
65,000,000$0.0066$0.0067$0.0035$0.0020$0.0200$0.0150
77,500,000$0.0058$0.0067$0.0035$0.0020
810,000,000$0.0052$0.0067$0.0035$0.0020
915,000,000$0.0044$0.0067$0.0035$0.0020
1020,000,000$0.0038$0.0067$0.0035$0.0020
1125,000,000$0.0034$0.0067$0.0035$0.0020
1230,000,000$0.0030$0.0067$0.0035$0.0020
1335,000,000$0.0024$0.0067$0.0035$0.0020
1440,000,000$0.0022$0.0067$0.0035$0.0020
Sheet1


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)
 
Upvote 0
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
 
Upvote 0
robertuva said:
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...
Book22
ABCDEFGH
1Volumecompany1company2company3company4Min
2250,000$0.0200$0.0240$0.0065$0.0020250,000
3500,000$0.0150$0.0170$0.0065$0.0020
41,000,000$0.0100$0.0121$0.0065$0.0020236,000500,001
52,500,000$0.0080$0.0121$0.0055$0.0020company1company2
65,000,000$0.0066$0.0067$0.0035$0.0020$0.0200$0.0121
77,500,000$0.0058$0.0067$0.0035$0.0020
810,000,000$0.0052$0.0067$0.0035$0.0020
915,000,000$0.0044$0.0067$0.0035$0.0020
1020,000,000$0.0038$0.0067$0.0035$0.0020
1125,000,000$0.0034$0.0067$0.0035$0.0020
1230,000,000$0.0030$0.0067$0.0035$0.0020
1335,000,000$0.0024$0.0067$0.0035$0.0020
1440,000,000$0.0022$0.0067$0.0035$0.0020
Sheet1


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))
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top