Results 1 to 11 of 11

20030207, 16:34 #1
 Join Date
 Feb 2001
 Posts
 109
 Thanks
 0
 Thanked 0 Times in 0 Posts
I Need Help with a Lookup (Excel 2000)
I have a 3column 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,

20030207, 17:14 #2
 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 Lookup (Excel 2000)
Try the ARRAY formula (confirm with ctrlshiftenter, not enter):
=SUM(IF((A2:A41=F3)*(B2:B41=F4),C2:C41))
Steve

20030207, 17:24 #3
 Join Date
 Jan 2002
 Location
 Tampico, Tamps, Mexico
 Posts
 118
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: I Need Help with a Lookup (Excel 2000)
Mark:
This is the array formula in F5 (remember press Ctrl+Shift+enter ).
=SUM((A2:A41=F3)*(B2:B41=F4)*(C2:C41))

20030207, 17:34 #4
 Join Date
 Feb 2001
 Posts
 109
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: I Need Help with a Lookup (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?

20030207, 17:47 #5
 Join Date
 Feb 2001
 Posts
 109
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: I Need Help with a Lookup (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?

20030207, 19:24 #6
 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 Lookup (Excel 2000)
RangeNames should work. Are all the range names "areas" the same size?
Steve

20030207, 19:30 #7
 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 Lookup (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

20030207, 19:57 #8
 Join Date
 Mar 2001
 Location
 Springfield, Ohio, USA
 Posts
 2,136
 Thanks
 0
 Thanked 2 Times in 2 Posts
Re: I Need Help with a Lookup (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
<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>

20030207, 20:00 #9
 Join Date
 Feb 2001
 Posts
 109
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: I Need Help with a Lookup (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,

20030207, 20:04 #10
 Join Date
 Feb 2001
 Posts
 109
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: I Need Help with a Lookup (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,

20030207, 20:24 #11
 Join Date
 Mar 2001
 Location
 Springfield, Ohio, USA
 Posts
 2,136
 Thanks
 0
 Thanked 2 Times in 2 Posts
Re: I Need Help with a Lookup (Excel 2000)
But, if you have the data in a 2D 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 2D table on Sheet2, then I used the Lookup Wizard to generate a lookup formula. HTH Sam
<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>