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

    I Need Help with a Look-up (Excel 2000)

    I have a 3-column worksheet where I need to perform a look based on the first to columns to return the result of the third column.

    The first Column contains the account number and they repeat. The second column contains the business unit and they also repeat. But each business unit will only have one instance of each account number, so together, they create a unique row number.

    A regular VLOOKUP will not work since there are two criteria and an INDEX MATCH formula does not seem to work since that combo is looking for vertical and horizontal arguments.

    I am attaching a spreadsheet that demonstrates how my lists are set up.

    If any one can provide a solution using formulas, it will be greatly appreciated.

    PS: I know that Access can do this very easily with a CrossTab query, but Access is not an option at this point.


    Thanks,
    Attached Files Attached Files

  2. #2
    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: I Need Help with a Look-up (Excel 2000)

    Try the ARRAY formula (confirm with ctrl-shift-enter, not enter):

    =SUM(IF((A2:A41=F3)*(B2:B41=F4),C2:C41))

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: I Need Help with a Look-up (Excel 2000)

    Mark:

    This is the array formula in F5 (remember press Ctrl+Shift+enter ).
    =SUM((A2:A41=F3)*(B2:B41=F4)*(C2:C41))

  4. #4
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: I Need Help with a Look-up (Excel 2000)

    I didn't specify it in my initial post, but I will be using named ranges. I tried your formula and it works if I use actual cell addresses, but if i use a named range instead, I get a #NUM! error. Is there a trick to using named ranges in an array formula?

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

    Re: I Need Help with a Look-up (Excel 2000)

    I have been using the array formulas and they work great except that the actual file I will be using will have 400 Account Numbers and 50 business units for a total of about 20,000 formulas. I tried a sample with all account numbers and only 8 business units and it took my workbook over a minute to recalculate. I am afraid to try it with all 50 business units.

    Is there another option besides array formulas?

  6. #6
    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: I Need Help with a Look-up (Excel 2000)

    RangeNames should work. Are all the range names "areas" the same size?

    Steve

  7. #7
    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: I Need Help with a Look-up (Excel 2000)

    If you want to make a table with "all" of these permutations, why don't you create a pivot table to do the summarizing.

    Your example showed given 1 account and 1 unit what is the value, I am not sure what 20,000 calcs you are trying to make?
    Steve

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: I Need Help with a Look-up (Excel 2000)

    If you want to speed things up, then rethink your workbook structure, add hidden columns, and eliminate the array formulas. In your example, I just created a hidden column C which contained A & B, then the formula was a simple VLOOKUP. HTH --Sam
    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: I Need Help with a Look-up (Excel 2000)

    In the example that I posted, I was just looking for the mechanics of the formula. On the final product, all of the account numbers will be listed down the left column (400 account number) and the business units will be listed across the top row (50 business units).

    I can't use a pivot table because once the table is built, there will be some more manipulations that have to be made.

    Thanks,

  10. #10
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: I Need Help with a Look-up (Excel 2000)

    I think you have the correct solution. I actualy thought of this myself after playing with the array formulas for a while.

    Thanks,

  11. #11
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: I Need Help with a Look-up (Excel 2000)

    But, if you have the data in a 2-D table, then you can use the Lookup Wizard (it's an AddIn) to generate a formula for you. In the attached workbook, I entered your "data" into a 2-D table on Sheet2, then I used the Lookup Wizard to generate a lookup formula. HTH --Sam
    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

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