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
Bookmarks