+ Reply to Thread
Results 1 to 3 of 3

need help with replacing indirect function in a vlookup

  1. #1
    whomail11
    Guest

    need help with replacing indirect function in a vlookup

    Hello,

    I am using a VLOOKUP formula incorporating the INDIRECT function and want to replace the indirect function with something else, perhaps a match, index, choose, offset or some other function. I cannot figure out a way to write this formula with a different function and need help. Here is a link to my file.

    The following formula in cell B7 is:

    =VLOOKUP(C4,INDIRECT(N7),2)

    Cell C4 is a value greater than 1.

    The formula in cell N7 is:

    =VLOOKUP(NUM,DATA,7)

    NUM is cell C2 and can be a value greater than 1. The contents of range name DATA is

    1 Asample D E F G AAA
    2 Bsample D E F G BBB

    This table can have up to 100 rows and 50 different values in column 7.

    The formula in N7 therefore returns AAA or BBB by matching the value of NUM in range DATA and looking up the contents of column 7. AAA or BBB are range names of other tables which are used in cell B7 thanks to the INDIRECT function. Using this function B7 does a lookup in table AAA or BBB.

    Table AAA looks like this

    0 5.75%
    100,000 4.75%
    250,000 3.75%
    500,000 2.00%
    1,000,000 0.00%

    Cell B7 will return a value equal to =LOOKUP(C4,BBB,2). If cell C4 is equal to 125,000 then the value returned in B7 is 4.75%. I cannot hardcode the range name of the table because the user can change the value of NUM.

    Any suggestions?

    Thanks for your help!!

  2. #2
    Forum Contributor
    Join Date
    10-14-2004
    Location
    San Diego, CA
    Posts
    213

    Re: need help with replacing indirect function in a vlookup

    Hello whomail11:

    Copy the table for ranges AAA and BBB to a new location (P2:Q12).

    Paste the IF() function in cell E1 and copy to cell G5; reference the tables in columns P and Q.

    =IF($C$2=1,P2,P8)

    (format the cells in column F to Percentage).

    Edit your formula in cell B7…. =VLOOKUP(C4,AAA,2)

    You can now delete the formula from cell N7 and the Named range BBB.

    Matt

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: need help with replacing indirect function in a vlookup

    you could use choose for up to 29 tables where NUM is 1to 29
    =CHOOSE(NUM,VLOOKUP(AMT,AAA,2),VLOOKUP(AMT,BBB,2),.............,VLOOKUP(AMT,xxx,2))
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1