Advance Vlookup in Microsoft Excel

Follow by Email

As we all know, Vlookup formula is used to lookup the value from the left most column. In advanced Vlookup function, we combine other functions for the improved outcome.


In this article, you will learn how you can use Vlookup to solve the problem in the advance way. This may even help you to create a dynamic dashboard.

Below is the image of Advance Vlookup (refer to Image 1).

In this Example, we have created helper column, defined the range name and on the basis of two lookup value we are getting the result.

image 1


Let’s understand from the start:-

We have data in which we have Customer name, product and quantity in the range A6:C15. In the other side, we have two drop down list: – first is customer name and second is product, so we want to retrieve the quantity as per the selection.

image 2


Step 1:Insert helper column,

Follow below given steps:

  • Insert a column before A column
  • Write the header name: Helper Column
  • Enter the formula: =B7&C7

image 3


  • Copy the same formula in the range A8:A15

image 4


Step 2:Define the range name

Follow below given steps:

  • Select the range A7:D15

image 5


  • Go to Formulas tab > Defined Names group > Click on Define Name

image 6


  • New Name dialog box will appear
  • Enter the range name
  • Select the range

image 7


  • Click OK

Step 3:Enter the formula

Follow below given steps:

  • Enter the formula in cell G8
  • =VLOOKUP(G6&G7,Range1,4,0)
  • Press Enter

image 8


  • This formula will give the result according to selected options, whenever you will change the selection from the drop down list result will get change

Formula Explanation: =VLOOKUP(G6&G7,Data,4,0)

1)    =VLOOKUP(G6&G7: This part of function will help to combine the Name and product for the lookup

2)    Range1: This is our array for which we have defined the name

3)    4,0: Pickup column through lookup and 0 is define to pick the exact value

home button
Previous VLOOKUP for multiple criteria
Next Retrieve entire row to matched value


image 48


If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at [email protected]

Please follow and like us:

One thought on “Advance Vlookup in Microsoft Excel

  1. I am interested in Tip 2 to solve my problem. Col A has names and Titles in it. Ex [LName],[Title], [FName]. Titles repeat, one to many. In Column E, I wanted certain titles inserted; otherwise leave blank. Is there a VLOOKUP formula that will handle that? Many Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

6 − = one

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>