Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Multi-column lookup (Excel 2003 SP1)

    Screenshot moved to zip attachment because it caused horizontal scrolling. Please don't post inages larger than 640x480 pixels. Also provided link to post (see <!help=19>Help 19<!/help>) and edited subject to be descriptive

    Hi sdckapr,

    I found your <post#=293687>post 293687</post#> regarding selecting data through multiple columns. It is exactly what I want however it doesn't seem to work on my PC. Do you know if there has been any change between Excel 2002 and 2003 that would affect this?

    I copied the table described and keyed in your formula. As shown in the attached screen shot...it seems to have all the right components, but keeps returning #VALUE.
    When i step through the formula it seems to run into problems with the ,($A$2:$A$21&$B$2:$B$21&$C$2:$C$21) part of the formula as all of those ranges return #VALUE.

    Thanks for any help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multi-column lookup (Excel 2003 SP1)

    Steve is not available at the moment, so I'll have a stab at it. As he indicated in <post#=293,687>post 293,687</post: >, the formula is an array formula. You should confirm it by pressing Ctrl+Shift+Enter, not by pressing Enter or by clicking the green check mark in the formula bar.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Thanks

    Thanks Hans,

    That solved the problem. I had minor problems converting it to my own spreadsheet, but finally managed to get it right. I've been trying to replace some VB code that a manager gave me as trying to get any support for the VB is difficult. This is a very handy formula and I can see many uses for it.

    Capri

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Thanks

    If you are going to use it a lot in the same table, I would suggest that instead of using the array formula to create the "intermediate" formula. that you add the intermediate column of concatenated columns and do a conventional Vlookup.

    That is:
    Insert a new col D between the current C and D.
    In the new D1 enter:
    <pre>=A1&B1&C1</pre>


    And copy (autofill) from D1 to D221
    Now instead of the array formula which is (now) in O1, use instead:
    <pre>=VLOOKUP(K1&L1&M1,$D$2:$H$21,N1+1,0)</pre>


    This method is faster and uses less memory (Array formulas are memory hogs and slow calcs down). This creates the intermediate formula once rather than everytime the array formula is used...

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Thanks

    Thanks Steve,

    I really appreciate your suggestion. The spreadsheet we are using currently has VB in to Read_tables, but the only person in our area who knows VB is the manager who created the initial formula. Several of us would like a backup method of doing the same thing in Excel just in case the manager leaves and we have no one to support the VB code. Several people can see many uses in other places for this type of formula and your solution saving memory and calculation time will come in very handy. Thanks.

    Capri

Posting Permissions

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