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

1. ## 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,

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

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

Steve

7. ## 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. ## 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

9. ## 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. ## 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. ## 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

#### Posting Permissions

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