+ Reply to Thread
Results 1 to 16 of 16

Aggregate/lookup function works with numbers in array but stops when a word is retrieved

  1. #1
    Registered User
    Join Date
    01-19-2019
    Location
    dillsburg, PA
    MS-Off Ver
    Office 365
    Posts
    18

    Aggregate/lookup function works with numbers in array but stops when a word is retrieved

    Hey folks,

    I am working with a function that contains both the aggregate function and the lookup function. This function has been working great for me. What it does is retrieves the amount of certain ingredients and inputs them into a spreadsheet beside the correct ingredient. An example of this would be if I had a list of ingredients like eggs, bacon, and toast. This function would autopopulate the numerical amount of each ingredient from another sheet beside the listed ingredient. HOWEVER, for some of the spices, I replaced the value (ex. 1/8 tsp) with a "pinch". Now, there is an alphabetic word that is being retrieved, rather than a number, and this threw the entire function off. The problem is that once the word "pinch" is retrieved for a certain ingredient, the rest of the ingredients that come after this specific ingredient will not autopopulate any more. I am assuming that either the aggregate or the lookup function do not work if there is a word being retrieved rather than a number. Or perhaps it is the "z" placeholder value that I am using as a lookup value that is throwing a wrench in things. If anyone has any advice, I would appreciate your help!

    The function that you see below is the last piece of the larger function seen in the images. The larger function found in the images is pretty just telling excel to retrieve the ingredient amount from a different location, which is why I only included the last function below. If I find a solution for just this last function, it should work for all of the functions if applied correctly.

    In the first image you see how in Column B there are numbers that correspond to an ingredient and then in Cell B8 the word "pinch" appears. This is because I made a function that converts a serving size of less than 1/4 tsp into a "pinch" to make things easier for the user. However, you see that this prevents data from populating below B8. But, when I clear the contents of B8 (as seen in the second image), this allows B9 to populate, which subsequently has the word "pinch" too which is correct. When you delete the contents of B9 (in image 3), you will see how the rest of the B column correctly autopopulates.

    This only leads me to assume that because it is a word and not a number, it doesn't allow the furtherance of the autopopulation!

    =IFERROR(MROUND(IFNA(INDEX(Recipes!$CR$1:$CX$1499,AGGREGATE(15,6,ROW(Recipes!$R$1:$R$1499)/(Recipes!$R$1:$R$1499=LOOKUP("z",B$1:B7)),ROW()-MATCH("z",B$1:B7)),MATCH(D$2,Recipes!$CR$1:$CX$1,)),""),INDEX(Lists!$E:$E,MATCH('Ex1'!D8,Lists!$B:$B,0),1)),"")

    image 1.png
    image 2.png
    image 3.png
    Last edited by dsteele111; 03-22-2019 at 01:32 PM.

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Aggregate and Lookup functions

    it would help if you could post a sample to illustrate, and explain in which cell the above is being entered into

    FWIW, the formula, as presented, is incomplete - either

    a) the final INDEX/MATCH is generating the MROUND and thus the alternate value for the IFERROR is missing, or
    b) the INDEX/MATCH is the alternative return for the IFERROR and thus the multiple parameter for the MROUND is missing.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,384

    Re: Aggregate and Lookup functions

    Administrative Note:

    We would very much like to help you with your query, however the thread title does not really convey what your request is about.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Registered User
    Join Date
    01-19-2019
    Location
    dillsburg, PA
    MS-Off Ver
    Office 365
    Posts
    18

    Re: Aggregate and Lookup functions

    Hello XLent,

    I believe that you a) suggestion is correct. The Iferror value is a blank "" that I did not include in the formula written. Above are illustrations that should help explain what is going on.

    Thank you for your help!

  5. #5
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Aggregate/lookup function works with numbers in array but stops when a word is retriev

    Right, so, putting aside potential to optimise the approach - the initial issue, in row 10, is most likely this:

    LOOKUP("z",B$1:B9)

    the introduction of "pinch" means the above is no longer returning "Casserole", and I presume you have "Casserole" (etc) in Column R on Recipe tab?
    {I'm also assuming you're using a number format to mask 0 - i.e. B8 & B9 actually contain 0, rather than ""}

    I'm not sure why you have the LOOKUP rather than a simple reference to B$2, given you reference B$2 explicitly in the column MATCH
    i.e. even if you copy this formula to another block lower down, for another recipe, you'd have to change the B$2 reference in the MATCH to B$x so you might as use B$2 in place of the LOOKUP

    again, the above is based on an image so take with a pinch of salt...

  6. #6
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Aggregate/lookup function works with numbers in array but stops when a word is retriev

    edit: my ageing eyes deceived me, the MATCH is using D$2 rather than B$2, so now I see value in using the LOOKUP
    (i.e. you can add another recipe lower down in Bx and generate a new ingredient listing)

    if you repeat "Units" header, for each recipe section (i.e. adjacent to Col B value), you could perhaps swap your current LOOKUP for:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    01-19-2019
    Location
    dillsburg, PA
    MS-Off Ver
    Office 365
    Posts
    18

    Re: Aggregate/lookup function works with numbers in array but stops when a word is retriev

    Hey XLent,

    Thanks for the response! You are correct in that the R column in the Recipe tab is a reference list of the recipe names. So say recipe 1 contains eggs and bacon. In a column beside "eggs" would be "recipe 1" and beside "bacon" would be "recipe 1". I guess it's a helper column.

    As far as masking the 0, in the second photo, I completely deleted the contents of cells B8 so there was no formula there whatsover. This is why B9 was allowed to populate. And when I deleted the formula from B9 in the third picture, which is why the rest of the ingredients could populate. So there is actually nothing there (athleast nothing in the formula header).

    Although I understand what you are saying for the lookup, I guess it is just one less number that I have to repeatedly insert, but you are not suggesting that this is the main problem, correct?

    If you are saying that by introducing the word "pinch" disrupts the return of the word "casserole", why do the numbers not disrupt this return? And how do you recommend that I go about making sure that the correct number is still returned?

    Thank you very much for your help.

    David

  8. #8
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Aggregate/lookup function works with numbers in array but stops when a word is retriev

    so, LOOKUP ignores values that are not the same data type as the criteria - so

    LOOKUP("Z",B$1:B9)

    will return the last text string in that range and ignore other data types

    your "pinch" problem is that your LOOKUP is no longer returning the recipe name, but "pinch" as this is the last text string found in the range
    given "pinch" is not a recipe it does not appear in Recipe!R:R and as such your subsequent rows are blank, the AGGREGATE won't return a row number

    so, per my earlier edit post, try, in row 10, swapping out

    LOOKUP("Z",B$1:B9)

    for

    LOOKUP(2,1/(C$1:C9="Units"),B$1:B9)

    this should pull Casserole -- as will pull the value in B associated with the last instance of Units in C range.
    Last edited by XLent; 03-22-2019 at 02:35 PM.

  9. #9
    Registered User
    Join Date
    01-19-2019
    Location
    dillsburg, PA
    MS-Off Ver
    Office 365
    Posts
    18

    Re: Aggregate/lookup function works with numbers in array but stops when a word is retriev

    image 4.png

    You can see here that I only have the word "Units" at the top, and the formula is pulled down throughout the document and I do not have to change it. List D:D is from another sheet that has the increments to be measured in "tablespoon, cup, etc), that corresponds to the ingredient in consideration.

    Are you suggesting that by changing the unit column, this would fix the lack of retrieving the column B references?

    David

  10. #10
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Aggregate/lookup function works with numbers in array but stops when a word is retriev

    Sorry, our posts are crossing -- if you can put Units next to Salad my suggestion should work, if you can't do that or prefer not to you can use Column D

    LOOKUP(2,1/(D$1:D9<>""),B$1:B9)

  11. #11
    Registered User
    Join Date
    01-19-2019
    Location
    dillsburg, PA
    MS-Off Ver
    Office 365
    Posts
    18

    Re: Aggregate/lookup function works with numbers in array but stops when a word is retriev

    image 5.png

    Here is the result of insertin the forumla into cell B10

  12. #12
    Registered User
    Join Date
    01-19-2019
    Location
    dillsburg, PA
    MS-Off Ver
    Office 365
    Posts
    18

    Re: Aggregate/lookup function works with numbers in array but stops when a word is retriev

    But the true answer should actually be "1/4" and not "1" according to image 3 from up at the top.

  13. #13
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Aggregate/lookup function works with numbers in array but stops when a word is retriev

    well, you need to modify the MATCH too (used to drive k in your AGGREGATE)

    MATCH("z",B$1:B9) --> MATCH(2,1/(D$1:D9<>""))

    {note I've assumed you will use the D construct rather than Units given Units is not repeated {for Salad} whereas D is populated}

  14. #14
    Registered User
    Join Date
    01-19-2019
    Location
    dillsburg, PA
    MS-Off Ver
    Office 365
    Posts
    18

    Re: Aggregate/lookup function works with numbers in array but stops when a word is retriev

    Okay, that makes sense. I think I modified it correctly now using the "Units" construct, however I'm not sure where it went wrong. I have no problem typing in Units beside each recipe title. Column D has numbers like "1.1" that help determine which column the data is retrieved from.

    image 6.png

  15. #15
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Aggregate/lookup function works with numbers in array but stops when a word is retriev

    OK; if using "Units" (Col C) to isolate Recipe row use:

    MATCH(2,1/(C$1:$C9="Units"))

    to be clear, the above is finding the last row containing Units, the below:

    MATCH(2,1/(D$1:$D9<>"")

    was designed to find the last non-blank in Column D to isolate Recipe row, applied to C it will generate erroneous value (last non-blank row in C).

    either/or of the above should work... I just went with D approach to save you having to remember to type "Units" in C adjacent to each Recipe heading.

    tbh, with a file (rather than an image) we could have closed this out some time back... so, if you've other follow ups, or want to see if you can tune the approach, post a file.
    Last edited by XLent; 03-22-2019 at 03:11 PM. Reason: apols for edits, I am multi tasking, poorly!

  16. #16
    Registered User
    Join Date
    01-19-2019
    Location
    dillsburg, PA
    MS-Off Ver
    Office 365
    Posts
    18

    Re: Aggregate/lookup function works with numbers in array but stops when a word is retriev

    Thank you very much for your patience and explanations XLent! You're a great help.

    With gratitude,
    David

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. INDEX and AGGREGATE to ignore 0's and functions
    By Allerdrengen in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 11-18-2018, 01:07 PM
  2. Index/Aggregate Function for Lookup
    By VirenS in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-15-2017, 12:09 PM
  3. Index/Aggregate Function for Lookup
    By VirenS in forum Excel General
    Replies: 0
    Last Post: 06-16-2016, 04:28 AM
  4. Macro to lookup data monthwise and aggregate the value in a new sheet
    By yemnirmal in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-13-2015, 05:36 AM
  5. Functions-help for aggregate tab
    By kmsoni in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-22-2011, 03:10 AM
  6. Loop Lookup Aggregate Total
    By fayzter in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-23-2009, 01:19 PM
  7. aggregate functions: problem with SELECT SUM()
    By xianwinwin in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-14-2007, 01:51 AM

Tags for this Thread

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