Javascript DHTML Drop Down Menu Powered by dhtml-menu-builder.com Active DataXL - Download   AnalyserXL - Download   DownloaderXL - Download   Smart VBA - Download   TraderXL - Download

Ozgrid Excel Newsletter. Excel Newsletter Archives  

VLOOKUP MEETING N conditions

 DOWNLOAD EXAMPLE

This month I would like to show you a quick and easy way to do a multi-condition 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 headings) enter;

=B2&"+"&C2&"+"&D2

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;

=IF(J1="","",VLOOKUP($J$1,$A$1:$H$9,5,FALSE))

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;

=$A2=$J$1

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.

EXCEL VBA: ADVANCED FILTER

 DOWNLOAD EXAMPLE

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 via VBA.

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.

A13=City
B13=Department
C13=Person.

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;

B1:H9=DataTable
A13:C14=Criteria
A16:H19=Results

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