Search functions: RANK, INDEX + MATCH or INDEX + MATCH

14/07/26 •0•0

Could there be a better way to search than VLOOKUP? Discover the combination of the INDEX function with MATCH in Excel or spreadsheets!

作者Leon

翻訳者Editorial

改訂者Editorial

RANK function

The RANK function returns the position of a specified value in a dataset.

RANK function syntax

=RANK(value; table_array; [order])

Where:

- value is the value whose ranking will be tested

- table_array is the range where the search will be performed;

- order is a TRUE or FALSE value for ASCENDING or DESCENDING respectively. FALSE will be considered if no value is entered.

Example

product	profit	Rank
TRANSPARENT PIPING BAG 30CM C/100UN	100.00	1
CELEBRATE CAKE STAND 5UN	93.69	2
PROFESSIONAL SILICONE MAT	52.72	3
WILTON 6-MUFFIN PAN	42.40	4
CELEBRATE TRANSPARENT PIPING BAG 45CM U	33.58	5
WILTON 12-CUPCAKE PAN	27.83	6
TIP # 6B OPEN STAR	24.00	7
DECORATIVE BRUSHES 922975	23.14	8
TRANSPARENT PIPING BAG 30CM (unit)	21.60	9

1. Ranking profit in descending order, what position does the product ROUNDED SMOOTHER occupy?

2. Ranking profit in ascending order, what position does the product WILTON 12-CUPCAKE PAN occupy?

Solution

1. =RANK(B3; B1:B11; FALSE) = 5

2. =RANK(B9; B1:B11; TRUE) = 10

INDEX function or ÍNDICE

The INDEX function returns the contents of a cell specified by row and column offset.

INDEX function syntax

=INDEX(table_array; row_num; [column_num])

Where:

- table_array is the range where the search will be performed;

- row_num is the number of the row being searched;

- column_num is the number of the column being searched;

Details to note:

- It is possible not to pass the column being searched as an argument; if you do not pass it, the entire row being searched will be returned

- It's like playing Battleship

Example

product	profit	Rank
TRANSPARENT PIPING BAG 30CM C/100UN	100.00	1
CELEBRATE CAKE STAND 5UN	93.69	2
PROFESSIONAL SILICONE MAT	52.72	3
WILTON 6-MUFFIN PAN	42.40	4
CELEBRATE TRANSPARENT PIPING BAG 45CM U	33.58	5
WILTON 12-CUPCAKE PAN	27.83	6
TIP # 6B OPEN STAR	24.00	7
DECORATIVE BRUSHES 922975	23.14	8
TRANSPARENT PIPING BAG 30CM (unit)	21.60	9

1. What data is in column 2, row 3?

2. What data is in column 3, row 1?

3. What data is in column 3, row 4?

Solution

1. =INDEX(A1:C15; 3 ; 2) = 2

2. =INDEX(A1:C15; 1 ; 3) = "Lesson"

3. =INDEX(A1:C15; 4 ; 3) = "Frequency distribution: collecting data in the field"

MATCH function or CORRESP

The MATCH function returns the relative position of an item in the range that matches the specified value.

MATCH function syntax

=MATCH(search_key; range; [search_type])

Where:

- search_key is the value to be searched for. For example, "42", "Cats" or "I24";

- range is the one-dimensional array to be searched.;

- search_type: "1" (default) finds the largest value less than or equal to the "search_key" when the "range" is sorted in ascending order. "0" finds the exact value when the "range" is not sorted. "-1" finds the smallest value greater than or equal to the "search_key" when the "range" is sorted in descending order.

Example

product	profit	Rank
TRANSPARENT PIPING BAG 30CM C/100UN	100.00	1
CELEBRATE CAKE STAND 5UN	93.69	2
PROFESSIONAL SILICONE MAT	52.72	3
WILTON 6-MUFFIN PAN	42.40	4
CELEBRATE TRANSPARENT PIPING BAG 45CM U	33.58	5
WILTON 12-CUPCAKE PAN	27.83	6
TIP # 6B OPEN STAR	24.00	7
DECORATIVE BRUSHES 922975	23.14	8
TRANSPARENT PIPING BAG 30CM (unit)	21.60	9

1. In which row is the product named "PROFESSIONAL SILICONE MAT"?

Solution

1. =MATCH("PROFESSIONAL SILICONE MAT"; A1:A11;1) = 10

INDEX + MATCH functions or ÍNDICE + CORRESP

The INDEX + MATCH combination provides the versatility that the VLOOKUP or HLOOKUP functions lacked.

INDEX + MATCH combination syntax

=INDEX(table_array;MATCH(search_key; range; [search_type]); column_num)

Example

product	profit	Rank
TRANSPARENT PIPING BAG 30CM C/100UN	100.00	1
CELEBRATE CAKE STAND 5UN	93.69	2
PROFESSIONAL SILICONE MAT	52.72	3
WILTON 6-MUFFIN PAN	42.40	4
CELEBRATE TRANSPARENT PIPING BAG 45CM U	33.58	5
WILTON 12-CUPCAKE PAN	27.83	6
TIP # 6B OPEN STAR	24.00	7
DECORATIVE BRUSHES 922975	23.14	8
TRANSPARENT PIPING BAG 30CM (unit)	21.60	9

1. What is the name of the product with profit R$ 27.83?

2. What is the profit of the product named PROFESSIONAL SILICONE MAT?

Solution

1. =INDEX(A1:C11;MATCH("27.83"; B1:B11;1); 1)

2. =INDEX(A1:C11;MATCH("PROFESSIONAL SILICONE MAT"; A1:A11;1); 2)

Exercises

Click here to access the spreadsheet to solve the next exercises.

1. What is the name of the product with code 102?

2. What is the name of the product with the third highest profit?

3. What is the name of the product that sold the second most in time 1?

Student Table:

Registration	Name	Course	Grade 1	Grade 2	Average
1001	João Silva	Mathematics	8.5	7.0	7.75
1002	Maria Oliveira	History	9.0	8.5	8.75
1003	Pedro Santos	Physics	6.5	7.5	7.0
1004	Ana Costa	Chemistry	7.0	8.0	7.5
1005	Carlos Mendes	Biology	5.5	6.0	5.75
1006	Luiza Fernandes	Mathematics	10.0	9.5	9.75
1007	Rafaela Almeida	History	8.0	8.0	8.0
1008	Bruno Pereira	Physics	7.5	7.0	7.25
1009	Fernanda Lima	Chemistry	6.0	7.5	6.75
1010	Gabriel Souza	Biology	9.0	8.5	8.75

Questions:

4. What is the average of the student with registration 1004?

5. What is the course of the student Luiza Fernandes?

6. What is the highest grade 1 among students in the Mathematics course?

7. How many students have an average greater than 8.0?

Inventory Table:

Product	January	February	March	April
Rice	500	450	480	490
Beans	300	280	290	310
Sugar	200	220	210	230
Coffee	150	160	155	165
Oil	100	110	105	115

Questions:

8. What was the stock of Sugar in the month of March?

9. What was the product with the lowest stock in the month of February?

10. What was the variation in Coffee stock between January and April?

11. What was the average stock of Beans over the four months?

Employee Table:

ID	Name	Position	Salary	Department
001	Ana Paula	Analyst	4500.00	IT
002	Carlos Eduardo	Manager	8000.00	Sales
003	Mariana Silva	Developer	5500.00	IT
004	João Pedro	Analyst	4200.00	HR
005	Luiza Costa	Manager	8500.00	Marketing
006	Pedro Henrique	Developer	6000.00	IT
007	Fernanda Lima	Analyst	4300.00	Sales
008	Rafael Souza	Manager	8200.00	HR
009	Bruna Oliveira	Developer	5800.00	IT
010	Lucas Mendes	Analyst	4100.00	Marketing