Vlookup function is a vertical lookup function which only lookup from left to right for a given range and criteria. =Vlookup (Lookupvalue, table array (range) , Column index number, 0 for exact match or 1 for partial match. )
Indirect function convert a text to a valid reference. =Indirect(“A2”) in this bracket A2 is stored as text but using with indirect it will convert the A2 into a cell reference and return the value exist in cell(A2).
In this example we will first create a Named Range dropdown list For Country And Item Name, Thereafter once we select country and item name from drop down list Vlookup and Indirect function will return price from particular county and respected item list. Please follow the steps shown below.
- Select the range of item list and price and give a name of country in name range menu bar. DO separately for all the country and give name according to it.
2. Go To Data Validation On The Data Tab.
3. Click On Data Validation And Select List From Drop Down Menu.
4. In The Source Textbox Either Type The Item List Name Or Select Directly As A Range Source.
6. Once Done, You Will Find The Dropdown List For Country And Item Price In The Center Table Listed Under County Table.
7. Now Apply Formula One Cell Down To Price Cell In A Table. =Vlookup(D10,Indirect(C10),2,0)
8. The Indirect Function Automatically Convert The Country Name Into Name Range Which We Have Define At First Step. If Not Using The Indirect It Will Recognize As A Single Name.
9. Now If You Select The Country You Will Have The Price As A Result From The Respected Country Table.
