# Get row reference from validated drop down list

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.

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

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