Dynamic VLookup in Excel No code example

Dynamic VLookup in Excel No code example

VLookup

Since we know Vlookup function returns a match within a Range array if found. But we can do more using VLookup, in this article we will do some super level tricks to manipulate Months and Sales based on users’ selection.

Step 1: create data as shown below:

Step 2: Create Validation List (Need how to create validation list please refer my previous article where you can create your validation list step by step here) as shown below for all the months:

Step 3: Put a formula to pull selected month cell as shown below:

  • $E$7: refers to constant month name
  • A1:B13: refers to Array within value (month name) needs to be looked up
  • 2: refers to column number which will be returns as part of result
  • FALSE: exact match is false

Step 4: Try to navigate or select different month name from the dropdown and sales figure would change respectively.

Wildcard (*) with Vlookup

Vlookup function can be used to perform wildcard search. A * (Star) sign should be passed as part of lookup to locate the value in the array table. Wildcard works in below direction when associated with the value as shown below:

Formula

Output

Next >> How to break Excel worksheet password a complete solution

Leave a Reply

Your email address will not be published.