Excel Formula Help

Discussion in 'General Software' started by Main Target, Jul 15, 2010.

  1. Main Target

    Main Target [H]ard|Gawd

    Messages:
    1,690
    Joined:
    Jul 5, 2004
    So I have an issue...Hopefully someone here is literate enough in Excel to help me.
    I am using this function to reference items in a table. The problem is the lookup value A9 changes, and I need the table array to change with it.

    So this is the function:
    Assume Cell A9 says Service, then this will work.

    =VLOOKUP($A9,Service_Array,3,FALSE)

    However if Cell A9 says Lease I need the function to dynamically change to say this:

    =VLOOKUP($A9,Lease_Array,3,FALSE)

    Somehow I need to reference A9 to change the table array option in this function.
     
  2. Snowknight26

    Snowknight26 [H]ardness Supreme

    Messages:
    4,160
    Joined:
    May 8, 2005
    =VLOOKUP($A9,IF($A9="Lease",INDIRECT("Lease_Array"),INDIRECT("Service_array")),3,FALSE)
     
  3. Main Target

    Main Target [H]ard|Gawd

    Messages:
    1,690
    Joined:
    Jul 5, 2004
    That works, but I should have mentioned that I have like 7 different options in A9
     
  4. Snowknight26

    Snowknight26 [H]ardness Supreme

    Messages:
    4,160
    Joined:
    May 8, 2005
    Substitute
    Code:
    IF($A9="Lease",INDIRECT("Lease_Array"),INDIRECT ("Service_array"))
    for something like
    Code:
    INDIRECT($A9 & "_Array")
    assuming that the names of your arrays match the text that's in A9.
     
  5. Main Target

    Main Target [H]ard|Gawd

    Messages:
    1,690
    Joined:
    Jul 5, 2004
    Okay, is it possible to use the contents of A9 to change the text before _Array?
     
  6. Main Target

    Main Target [H]ard|Gawd

    Messages:
    1,690
    Joined:
    Jul 5, 2004
    Arg, what you have provided is valid but I did not plan too far ahead and I think I have shot myself in the foot.

    I have an Items list that has:
    Services
    Lease
    Promos

    Then I have an Array called Services (Or leases or Promos) that has the following info:
    Voice Channel allows a concurrent call $100.00
    Weed Cleaning $200


    So imagine this....

    Cell A9 has the Item List. When I select Services, Cell A10 will populate with info from my array called Services. Cell A11 needs to populate with the price of that Item using the VLOOKUP function.