CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaspreadsheet
Luke Needham
Luke Needham

Reputation: 1

Google Sheets VLOOKUP formula stops working when new item values are added to the column being looked up

Hello stack overflowers.

I have recently been creating a nutrition tracker to better track and control my nutrition. However I have run into an issue. So currently I have a 1 sheet in the nutrition tracker which is a "database" of foods and their macro nutrients per gram. In this sheet, I will enter all the foods that I generally eat and their associated per G nutrients.

this food "database" sheet has the following columns.

FOOD NAME, CALORIES, PROTEIN, CARBS, OF WHICH SUGARS, FAT, OF WHICH SATURATED, FIBER, SALT

This database is then used as a reference, so that when I input each meal as I eat it, I can simply select the food from a drop down list and type the number of G in that meal, and the nutrients will all be calculated for me.

I currently have it setup so that I can select food from the drop down list generated by "foods" sheet, within each meal table I have created. This is then correctly filling in the rest of the columns as expected once I input a weight for each meal. There is however a huge problem.

As soon as the FOOD NAME column of the Foods sheet had values in it below row 7 (not sure why this row is the limit) the whole thing stops working, the data grabs based on VLOOKUP just return 0 and do not act as they are meant to. The strange thing is they work absolutely fine until I enter too many foods (7 foods) into the foods sheet.

Please find below a link to my spreadsheet, maybe you can duplicate it and play around a little yourselves to better understand the issue.

https://docs.google.com/spreadsheets/d/1orwih7s_Z4ew8G1vJcR6qlxyMpX8pqK-3Ynj42qQjcQ/edit?usp=sharing

(if you help me fix it, you will have a free nutrition tracking spreadsheet to help you take control of your diet aswell)

Thanks in advance.

Upvotes: 0

Views: 296

Answers (2)

player0
player0

Reputation: 1

try:

=INDEX(IF(B11:B18="";; IFNA(VLOOKUP(B11:B18; FOODS!A:I; COLUMN(B:G); ))))

Upvotes: 0

JPV
JPV

Reputation: 27312

In the June tab, clear all formulas in the range D11:K18.

Then enter in D11

=ArrayFormula(IF(LEN(B11:B18), IFERROR(VLOOKUP(B11:B18, FOODS!A:I, {2, 3, 4, 5, 6, 7}, 0)),))

This single formula will process all values entered in B11:B18.

Note the third parameter of VLOOKUP (set to false). If it is ommitted (as in your formula) it will default to 'true'. That means vlookup expects a 'sorted order' which may not be the case for your data.

enter image description here

References

Upvotes: 1

Related Questions