Google-sheets – Get row reference from validated drop down list

google sheetsgoogle-sheets-query

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.

Best Answer

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.

Logic

  • 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

ShoppingList

Sample data - Ingredients

Ingredients

Related Topic