How to use Advance Filter

Applies to MS-Excel 2003, MS-Excel 2007

In this session, we are going to discuss how to use Advance Filter when the Auto filter fails to give the desired results.

Picture 1: Custom AutoFilter

Custom Auto filter (Picture 1) will help us to filter data with the maximum of TWO filter criteria only. When we have more than TWO criteria, we have to use the Advanced Filter.

Before applying the Advance Filter what are all necessary information we should require?

1. Data Table
2. Criteria Table

1. DATA Table:

1. The FIRST ROW of the database should have HEADINGS.
2. There SHOULD NOT be any fully BLANK ROWS within the Database.
3. LAST COLUMN and LAST ROW should be BLANK. (Why is this necessary? While applying Advance Filter, system will automatically select the LIST RANGE by default)

Picture 2: Database Sample

2. CRITERIA Table

I have already mentioned that to filter the data with more than two criteria, we are using the Advanced Filter option. In Picture 2, we are having FOUR different products; out of these we are going to select THREE products as Filter criteria to filter the data.

How to define Criteria Table?

1. Select a blank cell in an unused part of the worksheet.
2. Give Criteria Title (It should be same as your database FIELD TITLE. Example: here the database field title is PRODUCT. The same title only we have to use as Criteria title. It will help the system to identify the field on which it has to apply the advance filter)
3. Mention all the THREE product’s names in the subsequent rows.

Sample of Criteria Table is given below:

Picture 3: Criteria Table

Both the basic things are ready.Now, how to apply the Advance Filter?

Select Data>Filter>Advanced Filter

Picture 4: Advanced Filter Dialog Box

MS-Excel, by default, will ask us to give the following two inputs:

1. List Range
2. Criteria Range

1. LIST RANGE:

To fix the List range:

1. By selecting the Advance Filter itself, System will select the List range automatically.
   
   
   

   

   Picture 5: List Range

2. If List range is not picked up automatically or if you want to restrict the data range, then you have to click Collapse Dialog button to choose the List Range.
3. Select the Database Table where you want to apply filter.

2. Criteria Range

To fix the Criteria range:

1. Click: Criteria Range Collapse Dialog button to choose the Criteria Range



Picture 6: Advanced Filter - Criteria Range

2. Select the Criteria table what you have created
3. Now Press Expand Button.

Picture 7: Advanced Filter - After basic Inputs

RESULT:

We can get the result in two ways:

1. Filter the list, in-place
2. Copy to another location

1. Filter the List, in-place

1. It is a default option.
2. Now, if we press OK Button, Advanced Filter will filter the data and show the result (Row index will be turned in BLUE colour) in the Database Table itself.

Picture 8: Filtered Data

 (See the row index 4,5,7,8 and 9 are not visible now)

2. Copy to another Location

1. Under Action, we have to select “Copy to another Location” option button
2. Now the “Copy to collapse dialog box” (as shown below) will be activated.



Picture 9: Copy to dialog

3. Here, we have to give the cell reference where the filtered result should be printed.
4. Press OK 

How to remove the filter:

Select DATA>FILTER>SHOW ALL

Please create the following table and try the exercise to get a clear idea about the Advance filter and feel the difference

Picture 10: Data Table

Exercise 1:

Picture 11: Exercise Number 1

Exercise 2:

Picture 12: Exercise Number 2

 Exercise 3:

Picture 13: Exercise Number 3

 Exercise 4:

Picture 14: Exercise Number 4

VLookup - New Usage

Can it be possible….? Friends, just go through the following case and tell me is there any possibility available to solve the issue with the help of VLOOKUP function.

A dealer wants to give a festival offer to his customers. He announces the following discounts.

Units	Product A	Product B	Product C
Upto 499	0%	5%	0%
>=500	0%	15%	5%
>=1000	10%	25%	10%
Price (Rs.)	10.00	8.00	25.00

Pick the appropriate discount rates for the following cases with the help of VLOOKUP function:

1. Z orders 100 units of product B
2. X orders 600 units of Product C.
3. Y orders 2500 units of product A.

Without looking at the solution try these cases... if you are unable to find the solution then read further.

VLOOKUP Function

Recall the syntax of VLOOKUP function. i.e., =vlookup(Lookup_value,table_array,col_index,range_lookup)

Here, we are giving FOUR Input parameters. Let us concentrate only on the fourth parameter now to solve this case.

Range_lookup

While using Vlookup function, we are used to assign FALSE (0) as Range_Lookup. What will be the result if I assign TRUE (1) as Range_Lookup?

• FALSE (0) as Range_Lookup: VLOOKUP will find the exact match, and if it is not found, it will return an error value (#N/A).
• TRUE (1) as Range_Lookup: This function will return the exact match, and if the same is unavailable, it will return the approximate match. In other words, if VLOOKUP can't find lookup_value, it uses the largest value that is less than or equal to lookup_value.

To solve the above said cases we have to use TRUE (1) as Range_lookup.

To illustrate, I am going to take the 3rd case - Y orders 2500 units of product A. (to explain the first 2 cases, we need some assistance from other function to get the result. We can get the result for the first 2 cases with the help of combined functions only.)

CASE 3:

Syntax is =vlookup(2500,A1:B4,2,1)

The function will return 10% as result as “2500” will not exactly match with the database. So, the function will look for the next highest value that is less than

2500.

To check this formula, you can try with some other units (say 499 units, 950 units or with exact values 0, 500 and 1000)

OTHER CASES

In the previous case, we have used 2 as column index to find the discount rate for 2500 units of Product A. we know that the details of Product A are available under column 2. How can we assist the system to find the column index automatically instead of feeding the column index manually?

MATCH Function

This function will return the relative position of an item from the selected area (i.e row or column)

Syntax: =match(lookup_value,lookup_array,match_type)

Let us now find the Column index of Product B

=match(“Product B”,A1:D1,0)

The function will return 3 as result.

COMBINED FUNCTION

Now we are going to solve the first 2 cases. For that we have to combine VLOOKUP function and MATCH function to get the desired result.

CASE 1:

Here we have two inputs i.e ITEM and ORDER QTY. First, we need to find the discount rate for Product B when the ordering quantity is 100 units.

Instead of giving column index manually, here we have inserted MATCH function to get the column index. (To test this function, change the item name as Product A or Product C)

Now, Try the rest (You already know how to use) to get the net value of the ordered quantity.

REMEMBER

• To get the accurate result from the VLOOKUP function, lookup_values (i.e. in this example “Units” column) should be in ascending order.
• If it is not so, the function may not find the largest value that is lower than the lookup value.