+ Reply to Thread
Results 1 to 55 of 55

Complex vlookup formula question

  1. #1
    Registered User
    Join Date
    10-28-2011
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    78

    Complex vlookup formula question

    Hey guys, first post here, and have a question.

    I am using Excel 2010

    What i need is to find the following information from another workbook, this book is a "units received" sheet so there is alot of data based on each day something is received at the warehouse. Many columns for; part #, date, qty, PO etc..

    1. Find the part #
    2. Find the date range I specify (ex.10/1-10/31)
    3. Sum the quantity received column for that date range

    End result I want; Give me the total received in that month for that part #.

    Is there a vlookup = sum(if) formula I can combine, or do I need something much more complex?

    Thanks for the help!

  2. #2
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Complex vlookup formula question

    SUMPRODUCT will do what you want

    =SUMPRODUCT(--(DateColumn >= StartDate), --(DateColumn <= EndDate), --(PartNumColumn = SelectedPartNum), QuantityColumn)


    Here is a great site that explains the SUMPRODUCT formula:
    http://xldynamic.com/source/xld.SUMPRODUCT.html

    You could also use SUMIFS(), but you mentioned that the data is in another workbook. SUMPRODUCT() will work on closed workbooks, whereas SUMIFS() will not.
    Last edited by Whizbang; 10-28-2011 at 10:32 AM.

  3. #3
    Registered User
    Join Date
    10-28-2011
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Complex vlookup formula question

    Thanks Whiz, that definitely sounds like what i need. Good read too. I put together the following formula and kept coming up with a "0" in the cell. I bolded the key areas. Does the sorting on the referenced book matter?


    =SUMPRODUCT(--('[PURCHASE RECEIPTS FILE (2009_2011).2011.01.05.xlsx]2011'!$A:$A>=10/1/11),
    --('[PURCHASE RECEIPTS FILE (2009_2011).2011.01.05.xlsx]2011'!$A:$A<=10/31/11),
    --('[PURCHASE RECEIPTS FILE (2009_2011).2011.01.05.xlsx]2011'!$E:$E=C2),'[PURCHASE RECEIPTS FILE (2009_2011).2011.01.05.xlsx]2011'!$G:$G)


    Thanks again!
    Last edited by Paul; 11-16-2011 at 02:35 PM. Reason: Removed quote of full post, unnecessary.

  4. #4
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Complex vlookup formula question

    Typing in a date will not work. Excel takes 10/1/11 to mean (10) / (1) / (11) which equals 0.909090909...

    I recommend you set aside two cells for your start and end date values. Then use:

    =SUMPRODUCT(--('[PURCHASE RECEIPTS FILE (2009_2011).2011.01.05.xlsx]2011'!$A:$A>=$A$1),
    --('[PURCHASE RECEIPTS FILE (2009_2011).2011.01.05.xlsx]2011'!$A:$A<=$A$2),
    --('[PURCHASE RECEIPTS FILE (2009_2011).2011.01.05.xlsx]2011'!$E:$E=C2),'[PURCHASE RECEIPTS FILE (2009_2011).2011.01.05.xlsx]2011'!$G:$G)

    You could also replace the dates with DATEVALUE("10/1/11")


    Sorting does not matter.

  5. #5
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Complex vlookup formula question

    Oh, and also I should mention that, for speed, you shouldn't use entire columns. Even though SUMPRODUCT isn't an array formula, in the sense that you don't have to confirm with CTRL+SHIFT+ENTER, it still calculates like one. In 2003 you wouldn't even have been able to reference the entire column. 2010 lets you, but it could result in slowness if you have very many of these formulas.

    Use dynamic named ranges. Here are a list of links that will explain them to you.

    http://www.ozgrid.com/Excel/DynamicRanges.htm
    http://www.excelhero.com/blog/2011/0...ing-index.html - The bit about named ranges is at the bottom of the article, but I strongly recommend you read the whole thing.
    http://support.microsoft.com/kb/830287

  6. #6
    Registered User
    Join Date
    10-28-2011
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Complex vlookup formula question

    Thanks again Whiz!!! Worked perfect.

    One last question. The receipts file has a column for vendors. Can I only sum / count the results of a certain vendor with that formula? Is that just an extra addition to the sumproduct formula? Like;

    =SUMPRODUCT(--(VENDOR COLUMN = "XXXX"),--(DateColumn >= StartDate), --(DateColumn <= EndDate), --(PartNumColumn = SelectedPartNum), QuantityColumn)
    Last edited by Paul; 11-16-2011 at 02:36 PM. Reason: Removed quote of full post, unnecessary.

  7. #7
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Complex vlookup formula question

    Yes, you have it right. This will limit the count to just that vendor, within that month, and that part number.

  8. #8
    Registered User
    Join Date
    10-28-2011
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Complex vlookup formula question

    Ok, so now I am getting an out of resources error.

    Will incorporating the dynamic named ranges fix this, or do i have another problem?

  9. #9
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Complex vlookup formula question

    My guess is that it would. Try it out and see what you get. It is always a good idea to limit your ranges to the smallest range possible. Excel 2007 and 2010 let you use entire columns in formulas that use arrays, but it is a bad practice.

    Try adding dynamic named ranges. If that doesn't fix it, upload a sample workbook and I will see what I can do.

  10. #10
    Registered User
    Join Date
    10-28-2011
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Complex vlookup formula question

    So I opened the workbook this morning, and my cells would not update. Not sure if this is realted to the resource issue, or not. I had the sumproduct formula copied across 3 cells in ea row that i'm using. I deleted those, and still get the resource error. My vlookup formula is across many rows, but I don't think that is the problem, as there is always date to be found.

    Here is a sample workbook. The first column has a clean(trim) formula in it. Not sure why, but I couldn't get my vlookups to work, so the IT guy added that column.

    The goal is to have the receipts row update as they are entered into the receipts workbook (which is a refreshable spreadhseet).

    Also, the forecast will change every month as a new one is released, and I will go in and update the formula to reflect (Column(D1)) into the new current start month. Is there a way around this? Ex. now OCT has D1 in the vlookup, but I now have a new forecast that will start with NOV, so D1, now needs to be in that cell.

    This workbook will get quite large I imagine, as I have several more projects to add to it.

    Also wanted to add, I tried to get away from referencing whole columns (most likely my problem), but the formula would no longer worked when I replaced $C:$C with $C1:$C40000. Is that too many rows to reference?
    Last edited by dta1984; 11-02-2011 at 06:22 PM.

  11. #11
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Complex vlookup formula question

    One thing about arrays is that all arrays in the same formula must be the same size.

    =SUMPRODUCT(--($A$1:$A$40000 = "Test"), --($B$1:$B$50000>0)) <-- This will not work. The arrays are not the same size. So, when you create your dynmaic named ranges, always count from the same range.

    Example of dynamic named ranges:
    =$A$1:INDEX($A:$A,COUNTA($A:$A))
    =$B$1:INDEX($B:$B,COUNTA($A:$A)) <-- NOT COUNTA($B:$B), since B may be of a different size. In order to be sure the arrays are the same size, always count from the same column.

    After looking through your attached workbook, I see that your data is contained in other workbooks. This is fine, but if, for whatever reason, those workbooks cannot be found or the user does not have read permissions, this will cause errors. I am not sure if the errors you are getting are related to these other workbooks or to the formulas you have in this one.

    Make some dummy data/sheets in the formula workbook, use named ranges to define this data. Then use the named ranges in your formulas. Once you get your formulas to behave the way you want, change the named ranges to point to the other workbook, instead of your dummy data. This way you can be sure the error resides in the other workbooks and not your formulas.

    Also be aware of the limitations of working with other workbooks. SUMPRODUCT and VLOOKUP will work just fine on closed workbooks, but not all functions will. INDIRECT will not work on a closed workbook, for instance. I am not sure if OFFSET will. My guess is not, but you'd have to try it or Google it to be sure.

  12. #12
    Registered User
    Join Date
    10-28-2011
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Complex vlookup formula question

    So far, I have changed my column references to just refer to a range of cells...like I tried to earlier but had no sucess. This time, it worked. As you said, I think all my arrays weren't the same range. It works now, and seems to have cured my resource error.

    Now, if I wanted my rowsthat will currently be unpolulated (ex. receipts for Dec) to automatically populate when the referenced workbook has an entry, will copying my formula into that cell really cause a problem? Or, should I just wait until Dec comes, and copy the formulas down then?

  13. #13
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Complex vlookup formula question

    I don't believe that copying the formulas down for months that do not exist will cause an issue. The VLOOKUP will return an error (I think #N/A) if no match is found, but you can either leave the #N/A error in place and trust that once December comes they will be replaced with values, or you can wrap the VLOOKUP in a IFERROR() function. The SUMPRODUCT formulas will just result in 0 until it finds matching rows.
    Last edited by Whizbang; 11-01-2011 at 01:46 PM.

  14. #14
    Registered User
    Join Date
    10-28-2011
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Complex vlookup formula question

    So far everything is smooth! Ok, now to take this a little further, can I sum two SUMPRODUCT formulas? Ex, I want to pull the SUMPRODUCT from one sheet, AND also look for this same conditions (date, vendor, sum qty) in another sheet (same workbook) at the same time. Can this be done ?

  15. #15
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Complex vlookup formula question

    There probably is a way to do it in a sinlge SUMPRODUCT formula, by just adding the two arrays, but the simplest way, I think, would be to just have two SUMPRODUCT formulas and then add them

    =SUMPRODUCT(sheet1...) + SUMPRODUCT(Sheet2...)

  16. #16
    Registered User
    Join Date
    10-28-2011
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Complex vlookup formula question

    Everything was working perfectly last night. Once again when I open my file this morning, none of the sumproduct formulas are updating, they all read 0. The referenced sheet is a refreshable spreadsheet. Do i need to change a setting in that spreadhseet maybe?

    When I click on Data > Edit Links, the link works properly.

  17. #17
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Complex vlookup formula question

    What happens when you hit F9, to manually recalculate the sheet? Did the data file names change? You say that the other sheet is a "refreshable spreadsheet", does that mean it has a pivot table or other form of query? If so, does the connection have the property "remove external data from worksheet on close" set to true?

  18. #18
    Registered User
    Join Date
    10-28-2011
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Complex vlookup formula question

    Hmm well I closed the workbook, and opened it back up and all the links work now. Weird.

  19. #19
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Complex vlookup formula question

    Was there any sort of "Enable Content" message at the top of your workbook when you opened it? Excel 2010 will ask you if you trust external data before it updates the formulas. Excel 2003 had a pop-up that you had to click a button to get past before you could work with the data. 2010 just gives you the little yellow bar. A lot of people miss it.
    Last edited by Whizbang; 11-02-2011 at 09:37 AM.

  20. #20
    Registered User
    Join Date
    10-28-2011
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Complex vlookup formula question

    For some reason, I don't get the yellow bar with the Enable Content message at the top. When I open the workbook, I get a message in the middle of the screen that says "This workbook contains links to other data sources" then I have the option to update or not update.

    Edit; so if I hit "Update" , I then get a message that says "This workbook contains one or more links that cannot be updated." When I hit the Edit Link selection, the status of them all shows OK, but the workbook isn't updating again.
    Last edited by Paul; 11-16-2011 at 02:37 PM. Reason: Removed quote of full post, unnecessary.

  21. #21
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Complex vlookup formula question

    Hmmm. If they cannot be updated, it is because it cannot find the files, or there is an error of some sort in the linked workbook.

    Try doing some Google searching using the exact phrase Excel gives you. Also, maybe open a new topic to see if we can attract some other people to help out. Usually people skip past a topic with many replies, simply because it means the original poster is already being helped. Since this error is not directly related to your original question, a new topic is acceptable.

  22. #22
    Registered User
    Join Date
    10-28-2011
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Complex vlookup formula question

    I was going to start a new thread for this question, but it kind of pertains to this thread.

    I am pulling data from a FC sheet with one of my VLOOKUP formulas. As a new FC sheet is released, I will go in and overide the current one in its present location. Now, the forecast will change every month as a new one is released, and I assume I will have to go in and update the formula in all the cells to reflect (Column(D1)) into the new current start month (D1 is now in OCT column, then drug across for the other 12 months.). D here is reflecting the 4th column in the FC sheet. My new current start month is NOV, so D1 needs to be there.

    Is there a way around this? Ex. now OCT has D1 in the vlookup, but I now have a new forecast that will start with NOV, so D1, now needs to be in that cell..

    OCT NOV DEC JAN FEB
    D1 E1 F1 G1 H1

    One idea I had was to go into the forecast sheet before I save it in my location, and insert a blank column before the fist month. That way, NOV would now be column E and my cells would automatically update on my data sheet. I'd just do this every month, inserting a blank column.

    Is there another way to do that?

  23. #23
    Registered User
    Join Date
    10-28-2011
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Complex vlookup formula question

    Hey Whiz, one last question. I have changed all my ranges to "dynamic" using index. Ex.:

    SUMPRODUCT(--(SHEET1'!$K$2:INDEX('SHEET1'!$K:$K,COUNTA(SHEET1'!$A:$A))="VENDOR"),--........etc.

    The formula won't update when my referenced workbook "Sheet1" is closed. Is this normal when using the INDEX function, or am i doing something wrong?

  24. #24
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Complex vlookup formula question

    No, INDEX should work just fine on a closed workbook. SUMPRODUCT as well. I don't believe your syntax is correct, though.

    =SUMPRODUCT(--([Book2.xlsx]Sheet1!$K$2:INDEX([Book2.xlsx]Sheet1!$K:$K,COUNTA([Book2.xlsx]Sheet1!$A:$A))="VENDOR"),...)

  25. #25
    Registered User
    Join Date
    10-28-2011
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Complex vlookup formula question

    I'm pretty sure that's how I have it set up, I just deleted the sheet / book names to save space. Here it is;

    SUMPRODUCT(--('\\192.168.110.20\Engineering\VAVE\Project Sheets\[PURCHASE RECEIPTS FILE (2009_2011).2011.01.05.xlsx]2011'!$K$2:INDEX('\\192.168.110.20\Engineering\VAVE\Project Sheets\[PURCHASE RECEIPTS FILE (2009_2011).2011.01.05.xlsx]2011'!$K:$K,COUNTA('\\192.168.110.20\Engineering\VAVE\Project Sheets\[PURCHASE RECEIPTS FILE (2009_2011).2011.01.05.xlsx]2011'!$A:$A))="VENDOR"),......

    The formula itself works good, but once I close the referenced workbook I get REF error. If I open the workbook back up, the cell populates the correct value.

    Edit; if it makes a difference, the referenced workbook is a refreshable spreadsheet.
    Last edited by Paul; 11-16-2011 at 02:38 PM. Reason: Removed quote of full post, unnecessary.

  26. #26
    Registered User
    Join Date
    10-28-2011
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Complex vlookup formula question

    I was going to upload an example workbook, but the file size is too large. Its 1.3mb, and I only have 1 formula in it. Why does the formula take up so much space? The workbook I want to use this formula in has hundreds of rows, I can only imagine what the size will be then.

  27. #27
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Complex vlookup formula question

    Does the refreshable worksheet have the "Remove external data from the workbook before closing" property checked in the connection properties?

  28. #28
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Complex vlookup formula question

    Quote Originally Posted by dta1984 View Post
    Why does the formula take up so much space?
    There is probably a cell somewhere thousands of rows down that contains some sort of value or property setting.

    Use the code below to select all the "Used Range". It will illustrate what I mean, I believe.

    Please Login or Register  to view this content.

  29. #29
    Registered User
    Join Date
    10-28-2011
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Complex vlookup formula question

    Quote Originally Posted by Whizbang View Post
    Does the refreshable worksheet have the "Remove external data from the workbook before closing" property checked in the connection properties?
    Hmm I don't see that option. I'll attach a screen shot of what I see under connections. The box to the right is what appears when I hit properties. The current connections are just where this workbook pulls data from I believe.
    Attached Images Attached Images

  30. #30
    Registered User
    Join Date
    10-28-2011
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Complex vlookup formula question

    Quote Originally Posted by Whizbang View Post
    There is probably a cell somewhere thousands of rows down that contains some sort of value or property setting.

    Use the code below to select all the "Used Range". It will illustrate what I mean, I believe.

    Please Login or Register  to view this content.
    Ok it found all data in the workbook. In the referenced book i have 35000 rows, which is what i expected. Is this too much for the INDEX function?
    Last edited by dta1984; 11-16-2011 at 10:37 AM.

  31. #31
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Complex vlookup formula question

    It looks that that option isn't enabled, so it is not that.

    Quote Originally Posted by dta1984 View Post
    In the referenced book i have 35000 rows, which is what i expected. Is this too much for the INDEX function?
    No. It should be fine.


    I am not sure what else to recommend, without seeing a sample workbook. I do not know why your formula workbook would be so large. There must be a bunch of data somewhere inside it. As to why the formula doesn't work when the data workbook is closed, I do not know. I would need to test that formula some more and see if I can reproduce the affect.

  32. #32
    Registered User
    Join Date
    10-28-2011
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Complex vlookup formula question

    I took out one of the sumproduct criteria I was searching for and got the workbook under 1mb. Here it is, let me know if this helps at all. I will keep searching on the ref workbook to figure out the not working on closed issue.
    Last edited by dta1984; 11-17-2011 at 11:56 AM.

  33. #33
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Complex vlookup formula question

    Ok. Got your solution. You cannot define the dynamic range within the formula workbook. Name the ranges in the data workbook, using the dynamic formula.

    E.G.
    DateColumn
    =$A$2:Index($A:$A,Counta($A:$A))

    Once your ranges are named in the data workbook, the formula workbook can use those named ranges even when the data workbook is closed.

    =SUMPRODUCT(--('\\192.168.110.20\Engineering\VAVE\Project Sheets\[PURCHASE RECEIPTS FILE (2009_2011).2011.01.05.xlsx]2011'!DateCol>=G2),--('\\192.168.110.20\Engineering\VAVE\Project Sheets\[PURCHASE RECEIPTS FILE (2009_2011).2011.01.05.xlsx]2011'!DateCol<=G3),--('\\192.168.110.20\Engineering\VAVE\Project Sheets\[PURCHASE RECEIPTS FILE (2009_2011).2011.01.05.xlsx]2011'!ColB>=H2),--('\\192.168.110.20\Engineering\VAVE\Project Sheets\[PURCHASE RECEIPTS FILE (2009_2011).2011.01.05.xlsx]2011'!ColE=E2),'\\192.168.110.20\Engineering\VAVE\Project Sheets\[PURCHASE RECEIPTS FILE (2009_2011).2011.01.05.xlsx]2011'!ColG)
    Last edited by Whizbang; 11-16-2011 at 04:08 PM.

  34. #34
    Registered User
    Join Date
    10-28-2011
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Complex vlookup formula question

    Awesome! Ok, i got the ranges defined in my ref workbook. Now when constructing the formula do i just use those names, or do I have to put "col" before or after the name? I tried both ways and got a NAME error.

  35. #35
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Complex vlookup formula question

    Use the names exactly as you defined them in your data workbook. "Col" was just because I didin't know the purpose for those columns, and so could not come up with a more descriptive name. You could just as easily use "Sales" or "CustomerNames" or "x". Obviously you want your names to be descriptive so you can more easily determine the source range when you read the formula. It makes the formula self documenting.

  36. #36
    Registered User
    Join Date
    10-28-2011
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Complex vlookup formula question

    Quote Originally Posted by Whizbang View Post
    Use the names exactly as you defined them in your data workbook. "Col" was just because I didin't know the purpose for those columns, and so could not come up with a more descriptive name. You could just as easily use "Sales" or "CustomerNames" or "x". Obviously you want your names to be descriptive so you can more easily determine the source range when you read the formula. It makes the formula self documenting.
    That's what I thought, just wanted to make sure my error wasn't coming from that. I got rid of the error, but it returns a "0" now.

  37. #37
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Complex vlookup formula question

    0 is good. It means that it is finding the ranges just fine, and calculating. It also means that no matches were found, though. So you'll have to determine if...

    1: Matches are supposed to be found
    2: If Yes to #1, then why weren't they found?

    It could be that your named ranges are pointing to the wrong ranges (ie doesn't include the whole column, for whatever reason, or points to the wrong column).

    It could also be that your values in your data sheet are of a different data type. Meaning the text "10/01/2011" is different than the date value of 10/01/2011. Make sure your entire column is consistent. One way to test this is to change from the syntax =SUMPRODUCT(--(Blah = Blah), --(Blah2 = Blah2),SumColumn) to =SUMPRODUCT((Blah=Blah)*(Blah2=Blah2)*SumColumn). The -- method ignores text values in numeric comparisons. Whereas the * method will throw an error if there is text in the values.

  38. #38
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Complex vlookup formula question

    I know I'm late to this party, but just a reminder that Excel 2007+ have more efficient functions, you might take a look at SUMIFS().
    Last edited by JBeaucaire; 11-17-2011 at 09:58 AM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  39. #39
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Complex vlookup formula question

    SUMIFS will not work on a closed workbook.

  40. #40
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Complex vlookup formula question

    Thanks, Whizbang. I totally didn't realize that. A little Googling turned up an explanation for that, too:

    Quote Originally Posted by barry houdini
    SUMIFS, SUMIF, COUNTIF and COUNTIFS only accept range arguments, not arrays. Typically any function applied to the range, TEXT or YEAR for instance, will result in an array......so best to stick with SUMPRODUCT.

    Incidentally this also explains why SUMIF and similar don't work with closed workbooks, because a range extracted from a closed workbook becomes an array.....

  41. #41
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Complex vlookup formula question

    Great! Thanks for the explaination. Sometimes you just have to accept that something has limitations, but it is always good to know the reason for the limitation.

  42. #42
    Registered User
    Join Date
    10-28-2011
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Complex vlookup formula question

    Quote Originally Posted by Whizbang View Post
    0 is good. It means that it is finding the ranges just fine, and calculating. It also means that no matches were found, though. So you'll have to determine if...

    1: Matches are supposed to be found
    2: If Yes to #1, then why weren't they found?

    It could be that your named ranges are pointing to the wrong ranges (ie doesn't include the whole column, for whatever reason, or points to the wrong column).

    It could also be that your values in your data sheet are of a different data type. Meaning the text "10/01/2011" is different than the date value of 10/01/2011. Make sure your entire column is consistent. One way to test this is to change from the syntax =SUMPRODUCT(--(Blah = Blah), --(Blah2 = Blah2),SumColumn) to =SUMPRODUCT((Blah=Blah)*(Blah2=Blah2)*SumColumn). The -- method ignores text values in numeric comparisons. Whereas the * method will throw an error if there is text in the values.
    Well, the problem turned out to be the cell I was selecting as my part # to match. I had to go in and add the (clean(trim)) formula like i did on my other sheet. The IT guy did that, but I have no clue what that formula does, or why I even have to use it. That solved the problem, and now I get the correct result. The name ranges really helped out, in making the formula more simplified, and also shrinking the size of the workbook!

    So it appears when I close the ref workbook, the # I pulled from the formula stays in the cell. I tried closing my workbook, then opening it back up. I get the Excel message about updating links. If I chose to update links, my cell now goes to REF. Is this normal, or should it still pull data from the closed ref workbook? I'll attach a screen shot of one of the error messages I get when I select upate links.
    Attached Images Attached Images

  43. #43
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Complex vlookup formula question

    CLEAN will get rid on "non printable characters". This will mostly get rid wierd characters that affect layout like line breaks or whatever. TRIM cleans up leading or trailing spaces or multiple consecutive spaces (eg " This sentence has too many spaces " will turn to "This sentence has too many spaces")

    As for the update error, I am not sure. I don't usually reference data from external workbooks. Or, if I do, they are more direct. Double-check your named ranges in your data workbook. What is the scope? What is the formula you used?

  44. #44
    Registered User
    Join Date
    10-28-2011
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Complex vlookup formula question

    Should my workbook store the most recent values though, until I open the ref workbook and hit refresh on it? I have a vlookup formula (with sumiferror) on the sheet and it pulls from a workbook (non refreshable) and I dont get any errors with it. I'm also going to attach the status message in the edit links menu when the workbok is closed.

    Here is the new formula;
    SUMPRODUCT(--('PURCHASE RECEIPTS FILE (2009_2011).2011.01.05.xlsx'!VENDOR_NAME="xxxx"),--('PURCHASE RECEIPTS FILE (2009_2011).2011.01.05.xlsx'!DATE>=G2),--('PURCHASE RECEIPTS FILE (2009_2011).2011.01.05.xlsx'!DATE<=G3),--('PURCHASE RECEIPTS FILE (2009_2011).2011.01.05.xlsx'!PO>=H2),--('PURCHASE RECEIPTS FILE (2009_2011).2011.01.05.xlsx'!PART_NO.=E2),'PURCHASE RECEIPTS FILE (2009_2011).2011.01.05.xlsx'!QTY)
    Attached Images Attached Images

  45. #45
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Complex vlookup formula question

    Yes, it will store the value and display that value until the link is updated.

    Double-check that VENDOR_NAME, DATE, PO, PART_NO and QTY all exist in the data workbook. Also, make sure the range/formula used is appropriate.

  46. #46
    Registered User
    Join Date
    10-28-2011
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Complex vlookup formula question

    Quote Originally Posted by Whizbang View Post
    Yes, it will store the value and display that value until the link is updated.

    Double-check that VENDOR_NAME, DATE, PO, PART_NO and QTY all exist in the data workbook. Also, make sure the range/formula used is appropriate.
    Here is the index formula i'm using on my referenced book for the named ranges;
    ='2011'!$A$2:INDEX('2011'!$A:$A,COUNTA('2011'!$A:$A))

    I had it working the other day (storing data when workbook was closed) before i used the named ranges. It kept jumping rows (because new data was populated) so they weren't the same when I would refresh the ref workbook, therefore giving me errors. This fixed the jumping rows issue, but I would like the data to remain.

  47. #47
    Registered User
    Join Date
    10-28-2011
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Complex vlookup formula question

    Just confirmed this by taking out the named ranges and using column ranges (a2:a35000). This works when the workbook is closed, and pulls data. Must be something with my index function in the named ranges.

  48. #48
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Complex vlookup formula question

    Try changing the scope of the named ranges to just the worksheet in question, then remove the sheetname from the formula.

    eg
    =$A$2:INDEX($A:$A,COUNTA($A:$A))

    The reason it is not working is the same reason that it wasn't working when this formula was still in the referencing workbook. The range address ends up looking like this:

    ='2011'!$A$2:'2011'!$A$35000

    The sheetname is not supposed to be on right side of the colon. That was why the named range needed to be in the data workbook. It was putting the workbook name on the right side of the colon when Index was done calculating.

  49. #49
    Registered User
    Join Date
    10-28-2011
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Complex vlookup formula question

    Quote Originally Posted by Whizbang View Post
    Try changing the scope of the named ranges to just the worksheet in question, then remove the sheetname from the formula.

    eg
    =$A$2:INDEX($A:$A,COUNTA($A:$A))

    The reason it is not working is the same reason that it wasn't working when this formula was still in the referencing workbook. The range address ends up looking like this:

    ='2011'!$A$2:'2011'!$A$35000

    The sheetname is not supposed to be on right side of the colon. That was why the named range needed to be in the data workbook. It was putting the workbook name on the right side of the colon when Index was done calculating.
    I see what you're saying. I went into the ref workbook to edit the named ranges, and when i delete the '2011'! part and hit save, it automatically puts it back in for some reason. I have the sheet selected under the scope dropdown.

  50. #50
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Complex vlookup formula question

    I dunno. I can't get it to work with the dynamic ranges. Maybe you'll just have to settle for manually updating the named ranges from time to time to accomodate growing data. Give yourself some breathing room and define the range out to 50,000 rows or something. Your calculation time won't be quite as quick, but you won't really notice it unless have dozens or hundreds of these formulas, or your data grows to 100k+ rows.

  51. #51
    Registered User
    Join Date
    10-28-2011
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Complex vlookup formula question

    Ok, just wanted to make sure I was doing things as efficient as possible. I added a hyperlink in the book so that the user can click on the link to open the ref workbook to get the updated info as needed. Works for me!

    One last q. I am wanting to use a formula to pull from the ref workbook and filter a part #, then an order #, and return a date (the date the order was received for that part#, and order#) to my workbook. Can i use sumproduct for this? Ex. SUMPRODUCT(--('SHEET1'PART=A1),--('SHEET1'ORDER=B1),'SHEET1'DATE

  52. #52
    Registered User
    Join Date
    11-17-2011
    Location
    Philippines
    MS-Off Ver
    MS Office 2010 to 2013
    Posts
    22

    Cool Vlookup question

    Hi,

    I'm working on a sheet and come across on a vlookup problem.

    I'm trying to use vlookup to extract the date from a cell with a date format of mm/dd/yyyy hh:mm:ss AM/PM, vlookup will only need the date information excluding the time for the lookup.

    I have attached a sample excel file. When the time is 12:00 AM, the lookup is working fine and shows the desired result, however when I changed the time, on the example, to 4:00 AM. It shows N/A as the result. It seems that it needs to match the time as well on the lookup. Is there a way to only use the date information and ignore the time. I needed this for a report and it needs to appear as it is on the sample spreadsheet attached.

    Any help would do. Thanks in advance.
    Attached Files Attached Files

  53. #53
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Complex vlookup formula question

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  54. #54
    Registered User
    Join Date
    11-17-2011
    Location
    Philippines
    MS-Off Ver
    MS Office 2010 to 2013
    Posts
    22

    Talking Re: Vlookup question (answered)

    Quote Originally Posted by rsbuslon View Post
    Hi,

    I'm working on a sheet and come across on a vlookup problem.

    I'm trying to use vlookup to extract the date from a cell with a date format of mm/dd/yyyy hh:mm:ss AM/PM, vlookup will only need the date information excluding the time for the lookup.

    I have attached a sample excel file. When the time is 12:00 AM, the lookup is working fine and shows the desired result, however when I changed the time, on the example, to 4:00 AM. It shows N/A as the result. It seems that it needs to match the time as well on the lookup. Is there a way to only use the date information and ignore the time. I needed this for a report and it needs to appear as it is on the sample spreadsheet attached.

    Any help would do. Thanks in advance.
    I have already found the answer, just to share:

    I set another column (D) where I could store the value of date on Date column using this formula:

    =IF($C3="","",MONTH(C3)&"/"&DAY(C3)&"/"&YEAR(C3))

    where MONTH, DAY and YEAR syntax will read the corresponding values on that cell;

    IF($C3="","" string will leave the cell blank if the value on Date column is blank.

    then:

    On the Group column, the formula is:

    =IF($C3="","",VLOOKUP(VALUE(D3),CALENDAR,2,FALSE))

    Wherein VALUE will, of course, read the value of the cell and thus load in on the vlookup. CALENDAR is what I named the Calendar cells.

    Some of you experts might have a better, easier, kickass approach on this one, but hey, I'm just intermediate on this one. LOL.

    I again attached the updated excel file for your reference. Thanks.
    Attached Files Attached Files

  55. #55
    Registered User
    Join Date
    11-17-2011
    Location
    Philippines
    MS-Off Ver
    MS Office 2010 to 2013
    Posts
    22

    Re: Complex vlookup formula question

    Quote Originally Posted by NBVC View Post
    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.
    Sorry about that. This is my first post so I'm not familiar with the rules. Thanks for being so hospitable anyway.

+ 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