|Assuming your data looks like this, follow the steps outlined below.|
A B C
1 Category Item Price
2 Veg Potato 20
3 Tomato 30
4 Carrot 10
5 Onion 15
7 NonVeg Chicken 60
8 Mutton 70
1 - Select B2:C5 and name the Range "Veg"
2 - Select B7:C8 and name the Range "NonVeg"
Now, let's assume you want to enter Veg or NonVeg in Sheet2!A1
3a - Select Sheet2!B1 and use Data...Validation...List
3b - In the Source box enter =INDEX(INDIRECT(A1),,1)
The INDIRECT function tells Excel that the text in A1 is actually the Name of a Range. The INDEX(ref,,1) function tells Excel to only use the first column of the Named range as the Source for the Data Validation Drop Down list.
4 - In C1 enter this formula:
The INDIRECT function will use the value in A1 as the Range for the VLOOKUP to lookup the value in B1 and return the value from the 2nd column, which should be your price.