The search function satisfies many conditions in Excel - Usage and examples

In the process of processing data in Excel many times, you need to search data that satisfies many conditions to extract data, so you are looking for a function that satisfies many conditions in Excel to use. The following article will share with you the detection functions that satisfy many conditions in Excel, please follow along.

Here are two ways to search for multiple conditions: VLOOKUP and sub columns and use INDEX in combination with MATCH.

How to use and example VLOOKUP function multiple conditions

You can see more syntax and usage of the Vlookup function here http://TipsMake.vn/ham-vlookup-in-excel/

Suppose you have the following data table, you want to look up the value in the Summary score column , based on the condition that the First and Last names column .

Specifically, you want to look up the Final score of the person named Tran Tuan Anh.

Problem solving:

You can use the Vlookup function to look up the condition that two columns by using the extra column. The secondary column will contain the contents of the First and Last Name column . So now the 2-condition Vlookup function will become the 1-condition Vlookup function. You can follow the specific steps as follows:

Step 1: Create an extra column, then you enter the formula using the & operator:

= C6 & "" & D6

or use the CONCATENATE function to concatenate strings in the First and Last Name column :

= CONCATENATE (C6; ""; D6)

Note: Secondary columns need to be created to the left of the Final Score column.

Step 2 : Copy the formula down the lines below to complete the extra column.

Step 3 : Continue to enter the formula Vlookup = VLOOKUP (C15 & "" & D15; E6: F12; 2; 0) with C15 & "" & D15 is a search condition matching 2 cells Tran Tuan Anh, E6: F12 is the detection table , 2 is the return value in columns 2, 0 is the relative lookup type.

Use the Index and Match functions to search multiple conditions

The syntax of the Index function is an array

= INDEX (array, row_num, [column_num])

You can see more how to use the Index function here http://TipsMake.vn/ham-index-trong-excel/

Syntax of Match function

= = MATCH (lookup_value, lookup_array, match_type)

You can see more how to use the Match function here http://TipsMake.vn/ham-match-ham-tim-kiem-mot-gia-tri-xac-dinh-trong-mot-mang-pham-en -o-in-excel /

Also with the above example, you can use the INDEX function in combination with the MATCH function to detect multiple conditions.

1. Enter the function = INDEX (C6: E12; MATCH (C15 & D15; C6: C12 & D6: D12; 0); 3)
2. Press Ctrl + Shift + Enter to convert to an array formula, curly braces {} will appear on the formula.

The MATCH function returns the position of a conditional row in a data table with:

1. C15 & D15 are two search values.
2. C6: C12 & D6: D12 are two columns containing the search value (array search).
3. 0 is the exact search type.

The INDEX function returns the value in the third column, the main row is the value returned by the MATCH function.

1. C6: E12 is the range of cells you need to search and return results.
2. The MATCH function is the index of the row from which to return a value.
3. 3 is the column index from which to return a value.

Above the article shows you two ways to detect two conditions in Excel, with requirements that require more conditions than you can apply similarly to perform. Hope this article will help you. Good luck!