Results 1 to 5 of 5

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. #1
    New Member
    Join Date
    Mar 2003
    Posts
    30

    Default 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. #2
    New Member
    Join Date
    May 2003
    Posts
    5

    Default 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. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,996

    Default 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
    =

    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. #4
    New Member
    Join Date
    Mar 2003
    Posts
    30

    Default 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. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,996

    Default Re: Pulling Information From One Worksheet Based On A Range

    Quote 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
    =

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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com