+ Reply to Thread
Results 1 to 9 of 9

Excel 2007 : Help combining VLOOKUP, MATCH, INDIRECT,etc.

  1. #1
    Registered User
    Join Date
    02-29-2012
    Location
    St. Louis, MO
    MS-Off Ver
    Excel 2007
    Posts
    6

    Question Help combining VLOOKUP, MATCH, INDIRECT,etc.

    Hey Everyone,
    Recently joined the forum to continue improving my Excel skills, and I have run into a bit of a snag using VLOOKUP. I think this situation is different than most since I haven't found anything that helps.

    I am working right now in a Sales database that is extremely big. I have reformatted all the data to play a lot nicer with PivotTables as this was a problem in the past. I have attached a basic example of what I need to do to finish breaking down numbers by divisions in the company.

    For most sales within the company, 100% of the revenue is attributed to that one division and manager. However, there are a few hundred types of sales that may have 2, 3, or 4 divisions receiving an allocation of the sales. As you can see in the "Allocation" tab, there are allocation %'s for each division based on the sales #, so when that sales # comes up, that is the allocation to use.

    Moving on to the "Sales" tab, it is very similar except has a ton of SAP data and new columns for Total Revenue, GM, etc. for each sale. This is the database for all the sales transactions. What I need to do is breakout these Total Revenues and such by their respective Division Allocations. VLOOKUP works fine for the divisions that recieve 100%: =Total Revenue*VLOOKUP(Sales#, AllocationTab, 7,FALSE), but I need to find a formula for the others. I start off the formula with =H2*IF(VLOOKUP(Sales#,AllocationTab,7,FALSE)=0,VLOOKUP(Sales#,AllocationTab,7,FALSE),.....

    This is where the problem comes in. The VLOOKUP will work for the first division for that sales number, but what formulas do I need to use with the VLOOKUP so that it references the West allocation of Sales #111 for example. I have looked at INDIRECT, MATCH, HLOOKUP, and more. Thank you very much in advance for your help.
    Attached Files Attached Files
    Last edited by beans19; 03-08-2012 at 01:26 PM.

  2. #2
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Help combining VLOOKUP, MATCH, INDIRECT,etc.

    beans19,

    Welcome to the forum!
    Attached is a modified version of your example workbook. I used a SumIfs formula because you're using a .xlsx file. So in the Sales sheet, cell L2 and copied down:
    =H2*SUMIFS(Allocation!$G$2:$G$7,Allocation!$A$2:$A$7,A2,Allocation!$F$2:$F$7,G2)

    As a side note, rows 9 and 10 seem odd to me. They are separated from the rest of the data and have a different date added. If they are their own table, just adapt the formula for their location.
    Attached Files Attached Files
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    02-29-2012
    Location
    St. Louis, MO
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Help combining VLOOKUP, MATCH, INDIRECT,etc.

    tigeravatar,

    First off, thank you. I modified the equation for my actual workbook and it works perfect so far, especially since I can drag it and it will continue onto other Sales #'s and equal 0 if the division isn't there or blank. I didn't realize it could be that simple and would've spent days writing complicated Lookups to figure it out. This is exactly why I am getting involved in the forums.

    I forgot to take the dates section you mentioned out before posting this as I wanted to figure this out first. Of course, to make things more complicated, now I have to take into account those dates. The division allocations will sometimes change. For example, in my Example sheet, The Jan allocation for Sales #111 may change to the new allocation that is added in March. I want to be able to add these new Allocations to the Sheet as a continuous record and then embed a formula that will check for the most recent date and use that allocation. So, from March on, the new allocation will be the one in rows 9 and 10. I'm already going to impress my boss with this since they couldn't figure it out, but again, any help would be much appreciated.

    Thanks in advance,
    beans19

  4. #4
    Registered User
    Join Date
    02-29-2012
    Location
    St. Louis, MO
    MS-Off Ver
    Excel 2007
    Posts
    6

    Thumbs up Re: Help combining VLOOKUP, MATCH, INDIRECT,etc.

    Definitely new to this. Just accidentally posted my reply twice.

  5. #5
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Help combining VLOOKUP, MATCH, INDIRECT,etc.

    beans19,

    I'm not 100% certain, but it sounds like you need to add a third condition to the sumifs, such that:
    1)it looks at the dates in 'Allocation' column H
    2)find dates on or before today's date
    3)use the date found as the criteria against column H

    This would mean that today, it would use the 1-Jan date because its not Mar-12 yet. However, starting Mar-12, it will use the 40% and 60% for 111 East and 111 West.

    Attached is version 2 using that logic. Let me know if I'm way off base here. To get the formula working correctly, I added the Jan-1 date to rows 5-7 on the Allocation worksheet. Formula used is:
    Please Login or Register  to view this content.

  6. #6
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Help combining VLOOKUP, MATCH, INDIRECT,etc.

    Also, it's important to note that the formula will change the old values to use the new values starting mar-12 and up. To prevent that (if you want that prevented), copy the cells you don't want to change, paste special -> values

  7. #7
    Registered User
    Join Date
    02-29-2012
    Location
    St. Louis, MO
    MS-Off Ver
    Excel 2007
    Posts
    6

    Lightbulb Re: Help combining VLOOKUP, MATCH, INDIRECT,etc.

    tigeravatar,

    Thanks for the formula to take into consideration the dates. Within the 100+ columns in this database I did find a Sales Date to use when referencing the allocation. Right now I have the same equation that you sent to me but just changed "Today()" to the column E which I have inserted and labeled Date of Sale. As you can see in both tabs I have played around with creating new allocations and sales dates to check its accuracy. So far it works great when dragging it down. It appears that this should work, but if you foresee any problems with this or referencing incorrect dates, please let me know.

    The only other question I have would be about creating a similar equation for the Manager and Division columns in the Sales tab. I have played around with LOOKUP and some others but can't figure out if it can be done without a circular reference. The yellow columns on the Sales tab represent SAP information. What I would like to do is when a new row for a sale is added, the SAP data can be copied 5 or 6 times down and then drag Manager, Division, and Revenue formulas down to automatically fill all Allocation data in. Afterwards, any rows without Manager, Division, or Revenue data can be deleted. This would just have to rely the unique Sales# and Date of Sale. At the very least it would help to get one column (with lookup or something based on the other), since there are multiple managers in each division.

    If you think this is possible let me know, otherwise thanks for your help.

    beans19

  8. #8
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Help combining VLOOKUP, MATCH, INDIRECT,etc.

    beans19,

    Quote Originally Posted by beans19 View Post
    if you foresee any problems with this or referencing incorrect dates, please let me know.
    That should work just fine


    Quote Originally Posted by beans19 View Post
    The only other question I have would be about creating a similar equation for the Manager and Division columns in the Sales tab.
    This should be possible, I'm just not sure what you want the equation to do. What should the output be, and what is the logic to reaching that output?

  9. #9
    Registered User
    Join Date
    02-29-2012
    Location
    St. Louis, MO
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Help combining VLOOKUP, MATCH, INDIRECT,etc.

    When new SAP information will be added into this Excel database, it will be just one row of Sales#,Product,Date,etc. for a transaction. What you have helped me with is breaking this out among various divisions which adds 2 or three rows for that Sales# to allocate. When I put the equation you sent me into my database, if I had a Division listed with the Sales# that wasn't included in the allocation, it filled it as 0. For example, if I had North, South, East, West as Divisions on the Sales tab for product 111 but the allocation lookup only resulted in North, East and West, the South Revenue=0 since there wasn't an allocation for it. I would like to do the same thing for the Manager and Division, basically a SUMIFS for this text similar to the Revenue based on the Sales# and Date.

    Lets say that someone enters this SAP data line for a transaction into the database:

    111 Product Location Date etc.

    Without having to search the Allocation tab to find out how many Managers and Divisions are allocated to this Sales#, they can Paste this data 5 or 6 times below it and drag the lookup formula for Managers and Divisions. As with the Revenue formula, if there are only 3 Managers and Divisions, the 4th line will be blank and each line from 4 on can be deleted.

  10. #10
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Help combining VLOOKUP, MATCH, INDIRECT,etc.

    beans19,

    Alright, I think I know what you're getting at. You want the Managers and Divisions to populate automatically based on Sales# and Date of Sale. This one is tricker because there are potentially multiple Managers and Divisions for a specific Sales# and Date of Sale.

    Attached is version 3.
    In the Sales tab, cell G2 (Managers) and copied down is this formula:
    Please Login or Register  to view this content.

    In the Sales tab, cell H2 (Divisions) and copied down, is this formula:
    Please Login or Register  to view this content.


    Note that the two formulas are practically identical except for what the Index location is. The Mangers formula indexes Allocation!$E$1:$E$12 and the Divions formula indexes Allocation!$F$1:$F$12.

    Its also important to note that, in the formula bar, the formula is surrounded in curly brackets {=formula}. This is because the formula is an array formula. To enter a formula as an array formula, it must be entered with Ctrl+Shift+Enter (CSE). Doing so will automatically put the curly braces around the formula (don't try to put them in manually).

    In the Sales tab, row 16, you'll notice that there is no Manager or Division. This is because that row is a 4th entry for Sales# 111 and Date of Sale 3/1/2011, but there are only three managers and divisions for that combination, so the formula doesn't return anything.

    Is something like that what you're looking for?

  11. #11
    Registered User
    Join Date
    02-29-2012
    Location
    St. Louis, MO
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Help combining VLOOKUP, MATCH, INDIRECT,etc.

    I copied and adapted your equations for the Manager and Division cells but wasn't able to return the correct reference. I didn't get any error messages, I just got blanks. I assume that means it worked but just wasn't referenced correctly.

    Either way I worked on putting this through the database and, since it's an array formula and very complicated, pretty much crashed Excel every time I adjusted anything because there are so many rows (my work computer is also a piece). We have plans to work around this so I'm going to leave this out as it will cause more problems than it could possibly solve.

    Thanks for your help tigeravatar. I have already implemented parts of these equations in other spreadsheets since I have a much better understanding of them now. I'll chalk this one up to solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1