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

Topic

New Reply

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,

Reply | Quote

Replies

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

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

Steve

Reply | Quote

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?

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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,

Reply | Quote

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

Reply | Quote

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

Thanks,

Reply | Quote

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

Reply | Quote

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

Steve

Reply | Quote

Mark:

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

Reply | Quote