Search functions: Filters, OFFSET and INDEX:INDEX

07/14/26 •0•0

In this class we will see how to reduce your database in Excel or spreadsheets so that we can only use what we want

Written byLeon

Translated byEditorial

Revised byEditorial

Depending on the type of situation you are in, you may not want to work with your entire dataset. In previous classes, we saw questions like "What is the second product with the highest profit within time 1?". This question becomes quite complicated since we first have to reduce our database only to time 1 and then rank the products according to profit.

So let's look at ways to reduce the database.

Filtering your data

Excel or Google Sheets has the option to "Create filter". Follow the steps below to create one:

Select your data

After selecting it, click on "Data"

Then click on "Create filter"

A symbol will appear next to each name; click on that symbol

Let's create a new filter; we only want to show what is in time 1! To do this, you need to clear all filters and then click on "1" to leave only the values with time 1 in the table.

The problems with filters

The filter does not remove the data you do not want to work with; it only hides it from view. That is, when applying a function such as =MAX(A2:A), which would find the maximum time, it would still return 12 (the maximum time in the table), because the data is still there.

The filter is very important when we want to get a quick idea of what is happening in a large database, leaving only what matters visible. But we should mention here that it still does not help answer questions that require removing part of your database.

OFFSET Function

The OFFSET function returns a shifted range reference by a specified number of rows and columns from an initial cell reference.

OFFSET function syntax

=OFFSET(cell_reference, rows_to_shift, columns_to_shift, [height], [width])

Where:

- cell_reference is the point from which to count the rows and columns to be shifted.

- rows_to_shift is the number of rows to shift.

- columns_to_shift is the number of columns to shift.

- height - [optional] the height of the range that should be returned from the specified offset.

- width - [optional] the width of the range that should be returned from the specified offset.

Example

Time	Sales
1	200
2	250
3	300
4	150
5	300
6	200
7	230

1. What is the lowest sales value when time is greater than or equal to 4?

2. What is the highest sales value when time is greater than 2 and less than 6?

Solution

1. =MIN(OFFSET(A1:B8,4,1)) or =MIN(OFFSET(A1:B8,MATCH(4,A:A)-1,1))

2. =MAX(OFFSET(A1:B8,4,1,2,1)) or =MAX(OFFSET(A1:B8,match(2,A:A),1,match(6,A:A) - match(2,A:A) - 1,1))

INDEX:INDEX Function

The INDEX function returns the content of a cell specified by the row and column offset. It actually returns the cell itself that is being shifted. That is, you can use the INDEX function with another INDEX function to specify a part of your database.

INDEX:INDEX function syntax

=INDEX(reference, [row], [column]):INDEX(reference, [row], [column])

Where:

- reference is the array of cells to shift.

- row - [optional] is the number of rows to shift.

- column - [optional] is the number of columns to shift.

Example

Time	Sales
1	200
2	250
3	300
4	150
5	300
6	200
7	230

1. What is the lowest sales value when time is greater than or equal to 4?

2. What is the highest sales value when time is greater than 2 and less than 6?

Solution

1. =MIN(INDEX(A1:B8,5,2):INDEX(A1:B8,8,2)) or =MIN(INDEX(A1:B8,MATCH(4,A:A,0),2):INDEX(A1:B8,MATCH(MAX(A:A),A:A,0),2))

2. =MAX(INDEX(A1:B8,4,2):INDEX(A1:B8,6,2)) or =MAX(INDEX(A1:B8,MATCH(3,A:A,0),2):INDEX(A1:B8,MATCH(5,A:A,0),2))

SORT Function

The SORT function in Excel allows you to sort a data range based on one or more columns. It is a dynamic function that returns a sorted range of cells without needing to change the original data structure.

=SORT(array; [sort_index]; [order]; [by_column])

Where:

array: is the range of cells or table you want to sort.

[sort_index]: (optional) is the number of the column or row by which you want to sort. If omitted, sorting will be done by the first column or row.

[order]: (optional) defines the sorting order:

1 for ascending order (default).

-1 for descending order.

[by_column]: (optional) defines whether sorting will be done by column (TRUE) or by row (FALSE, default).

Practical Example

Let's use the following sales table as an example:

Salesperson	Region	Sales
João Silva	South	5000
Maria Oliveira	North	3000
Pedro Santos	Southeast	7000
Ana Costa	South	4000
Carlos Mendes	Northeast	6000
Luiza Fernandes	South	5500
Rafael Almeida	North	4500

Objective: Sort the table by the Sales column in ascending order.

=SORT(A2:C8; 3; 1)

Objective: Sort the table by the Sales column in descending order.

=SORT(A2:C8; 3; -1)

FILTER Function

The FILTER function in Excel allows you to extract a dataset from a table based on specific criteria. It is a dynamic function that returns a range of cells that meet the defined conditions, without needing to use complex formulas or manual filters.

=FILTER(array; include; [if_empty])

Where:

- array: is the range of cells or table you want to filter.

- include: is the condition or criterion that defines which rows will be included in the result.

- [if_empty]: (optional) is the value returned if no data meets the criterion. If omitted, the function will return an error.

Important Details

1. The FILTER function returns a dynamic range, meaning the result can include several rows and columns, depending on the criteria.

2. If the criterion is not met by any data, the function will return an error unless the `[if_empty]` argument is specified.

3. The function is case-sensitive unless you use additional functions to ignore this sensitivity.

Practical Example

Let's use the following sales table as an example:

Salesperson	Region	Sales
João Silva	South	5000
Maria Oliveira	North	3000
Pedro Santos	Southeast	7000
Ana Costa	South	4000
Carlos Mendes	Northeast	6000
Luiza Fernandes	South	5500
Rafael Almeida	North	4500