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.
- Enter the function = INDEX (C6: E12; MATCH (C15 & D15; C6: C12 & D6: D12; 0); 3)
- 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:
- C15 & D15 are two search values.
- C6: C12 & D6: D12 are two columns containing the search value (array search).
- 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.
- C6: E12 is the range of cells you need to search and return results.
- The MATCH function is the index of the row from which to return a value.
- 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!
You should read it
- Offset function in Excel - Usage and examples
- DCOUNT function in Excel - Usage and practical examples
- IRR function in Excel - Usage and examples
- Multiple if function - Usage and examples
- VLOOKUP function to use and specific examples
- Match function in Excel - Usage and illustrative examples
- Excel date function - Usage and examples
- Function Address - The function returns the address of a cell in Excel (usage, examples, examples)
- OR function in Excel, how to use the OR function, and examples
- PMT function in Excel - Usage and examples
- How to use Hlookup function on Excel
- FIND function in Excel - Usage and examples
Maybe you are interested
How to turn a photo into a painting using the Generative Fill function in Photoshop
How to use the TREND function in Excel
Google Sheets Functions to Simplify Your Budget Spreadsheets
Instructions for using the TRIMRANGE function to clean up Excel tables
How to master numerical data in Google Sheets with the AVERAGE function
Don't buy headphones if they lack this important function!