This month I would like to show you a quick and easy way to do a
VLOOKUP. As you may know, VLOOKUP normally looks up data in the 1st column
of a data table (Note that table should be sorted in ascending order by the
first column) and returns the corresponding column we specify. Say we have a
table of data in A1:G9 and we wanted to VLOOKUP based on our 1st 3 Columns.
Insert a new column at column A so your table now occupies B1:H9. We will now
use this new Column to Concatenate our 1st 3 Columns. So in A2 (Row 1 are
and then copy down to the last row of our Table. This Column ("A") can now be hidden (optional)
Now we will create a Data Validation list to show our Concatenated Columns. So in any cell, outside our table (I'm using J1), go to Data | Data Validation. Choose the "List" option from the "Allow:" box and set our "Source:" box to A2:A9. Now in J2 enter this VLOOKUP Formula;
Now, as we choose values from our Data Validation list we can return a value that correspondence with multi-lookup-conditions.
Let's make it even more user friendly by highlighting the table row that meets 3 conditions in J1. Highlight/Select A2:H8 and then activate Conditional Formatting and use the Formula option. In the formula box, use;
Set your Fill color to any color, Yellow is my preference, and OK it. It is important to note that $A2 is a Relative row and Absolute Column reference. This forces the whole table row to be highlighted.
While the above VLOOKUP allows us to return a VLOOKUP that meets 3
conditions, it doesn't return all columns that meet our condition, or all rows
(assuming more than 1 record meets the 3 conditions). To return all columns and
rows we can automate Advanced Filter
For the purpose of the exercise I'm going to use cells directly below our table that occupies A1:H9. In cells A13:C13 we enter copies of our table headings.
Directly below these cells we are going to use Data Validation lists that refer back to our data directly below our table headings.
Now we name our ranges;
The VBA code;
Sub GetData() Range("Results").Clear Range("DataTable").AdvancedFilter xlFilterCopy, Range("Criteria"), Range("Results")(1, 1) If Range("Results")(2, 1) = vbNullString Then Range("Results").Clear MsgBox "No results matching criteria", vbInformation End If End Sub
It is important to note that will first clear any data in our named range "Results". We do this to ensure there is no data in the "Results" range as Advanced Filter will throw a Run-Time error if it copies results to a range with data already in.
It is also worth noting the check for data (not headings) is via;
If Range("Results")(2, 1) = vbNullString Then
The (2, 1) is the Item method and refers to the second row, 1 column in our Named range "Results". If no data, we clear the entire Named range "Results". Advanced Filter will still copy our table heading even though no data meets our criteria. This can confuse the end user so we display a MsgBox.
Excel Dashboard Reports & Excel Dashboard Charts 50% Off
Become an ExcelUser Affiliate & Earn Money
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft