VLOOKUP in array formula????

[ChristopherM]

I am trying to construct a spreadsheet to convert sales from a list of old departments to new departments. Each new department represents between two and twenty old departments. I started with sheet 1 with column A containing the old department (olddep) numbers and column B the olddep sales. Sheet2 Column A contains the newdep numbers and column B a formula such as =VLOOKUP(...) + VLOOKUP(...) + VLOOKUP(...) etc This has been very hard to debug when the two sheets don't come to the same total. I have been told I should have a third sheet where column A is the newdep and columns B,C,D etc the olddeps that make it up, then use an array VLOOKUP in the newdep sales sheet which will use the info in the conversion table to total the relevant olddep sales.

But I have no idea if this is really possible and if so how it should be done. Anybody got any ideas? Thanks in advance for your help.

 

[smartbutdull]

Don't know about an array VLOOKUP, but try this:

Suppose you have up to three old departments in one new department (this will work for any number though).

Enter the new departments in column A.
In column B start at 1 against the first new department and 2 against the second etc
Enter all of the old departments in columns C to E.



eg
A B C D E
new1 1 old1 old2 old3
new2 2 old4 old5 old6
etc...


then try the following formula:
=OFFSET(xxxx,SUMPRODUCT((yyyy=$C$6:$E$8)*$B$6:$B$8),0)

where xxxx is the cell in column A immediately above the cell containing the first new department

and yyyy is the cell containing the old department that you want to replace


Hope this helps,

Phil

 

[KenWright]

You obviously have a mapping somewhere of Old depts to New depts, so lets start with that. Name a sheet WS3 and in Col A starting A2 (Title assumed in A1), put all the old depts. Now in Col B put the new corresponding dept number.

Now select the entire table, titles and all, and name it Depts using Insert / Name / Define

Now, on your Sheet of Old depts with sales data (call it WS1), you can now use a VLOOKUP formula to convert all the old dept names to new dept names:-

With your dept names in Col A and dept sales in Col B, insert a new column between them, and we will put a VLOOKUP formula in here to do the mapping:-

In cell B2 on WS1 (Again assuming titles in row 1), put the following:-

=VLOOKUP(A1,Depts,2,0) and then copy down (With cell B2 selected, simply double click the bottom right hand corner of the cell where you see the black cross, and it will autofill).

Now select all of Col B, do Edit / Copy, then Edit / Paste Special / Values. You can either delete Col A now, or leave it for reference purposes. I'm going to assume you leave it as is for the moment.

Now you have three ways of grouping this data, and you should really try all, as all will be good learning exercises:-

1) Use a Pivot table. Select B1:C10000 or whatever your last cell is, then do Data / Pivot Table and Pivot Chart report / Next / Next / Finish

Drag the field in the box that will appear that is marked Dept over to the very left hand side of the table where it says ROW, and then just drag the field that says Sales into the big area marked Data.

You can then play with it like that, or copy and paste special to another sheet and leave it as a flat table.

There is a really nice intro to these at the following link:-

http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm

2) You can use Advanced Filter to group your data:-

Select B1:B1000 or whatever, and do Data / Filter Advanced Filter, Check 'Copy To Another Location', Use the select option on the 'Copy To' and select say cell B1010, or any cell below your existing list. Check the 'Unique values only' box and hit OK.

This will put a list of your depts below your table of data, although you may well already have this data somewhere else that you can copy, but it is a good learning exercise on how to get a list of unique values.

Now simply use SUMIF to collate the sales data, eg, assuming your list of depts now runs from B1010 to B1020, in cell C1010 put the following formula and copy down to C1020:-

=SUMIF($B$1:$B$1000,B1010,$C$1:$C$1000)

Job Done.

3) Select the data B1:C1000, and then sort on Col B in ascending/descending order.

Now with the same area still selected, do Data / Subtotals, At each change in 'Depts', put subtotal against 'Sales'. Then when done, you can simply click on the 1,2,3 at the left to Contract/Expand the list to either detail or summary level.

If you have any problems then just post back.

Regards
Ken....................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------