So I have two sheets, masterIngredientList and shoppingList. I have data validation in my shoppingList which is bound to a column in the masterIngredientList.

In the master ingredient sheet, there are costings in lets say column 'G'. So if the user selects Onion in shoppingList, in the adjacent column I'd like to get that cost for that ingredient.

I've tried to search but to no avail.


Try this formula in cell B3 of the shopping list:

=ARRAYFORMULA(IF(LEN(A3:A), VLOOKUP($A3:A,wa_153091_ingredients!A2:G,7,FALSE),))

The formula will automatically populate all items on the shopping list.


  • Arrayformula automatically applies the formula to every row in column B.
  • VLOOKUP($A3:A takes the value on each row in column A and searches for the same value in the first column of the range A2:G (Column A) on sheet="wa_153091_ingredients"
  • index=7 (return the 7th column (G) =Price)
  • is_sorted=false: the match must be exact.
  • IF(LEN(A3:A) tests for a value in Column A so that vlookup applies to rows with a value rather than every row in the sheet.

Sample data - Shopping List


Sample data - Ingredients


  • looks good, could you tell me how to read that formula? – marwaha.ks Mar 30 at 9:10
  • All the (unnecessary) curly brackets in that formula - argh. I've updated it for simplicity. I've also added an explanation of the logic behind the formula. Let me know if you need more info. – Tedinoz Mar 31 at 5:40
  • Cheers buddy!!! – marwaha.ks Mar 31 at 13:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.