Buzz
Explore more on the syntax and usage of the Vlookup function at https://Mytour/ham-vlookup-trong-excel/
Imagine you have the following data table, and you want to look up the value in the Final Score column based on the conditions in the Last Name and First Name columns.
Specifically, you want to look up the Final Score of the person named Tran Tuan Anh.
Problem Resolution:
Utilize the Vlookup function to search with the condition of two columns by incorporating an additional column. This auxiliary column will contain a combined content of the Last Name and First Name columns. Consequently, the Vlookup function with two conditions becomes a Vlookup function with a single condition. Follow these specific steps:
Step 1: Create an auxiliary column, then input the formula using the & operator:
=C6&' '&D6
Alternatively, use the CONCATENATE function to concatenate strings in the Last Name and First Name columns:
=CONCATENATE(C6;' ';D6)
Note: The auxiliary column must be created to the left of the Final Score column.
Step 2: Copy the formula down the rows below to complete the additional column.
Step 3: Continue inputting the Vlookup formula =VLOOKUP(C15&' '&D15;E6:F12;2;0) with C15&' '&D15 as the condition to search for the combined cell of Trần Tuấn Anh, E6:F12 as the lookup table, 2 as the return value in column 2, and 0 as the exact match lookup type.
Utilize Index and Match functions to search with multiple conditions
Syntax for array form of the Index function
=INDEX(array,row_num,[column_num])
Explore additional usage of the Index function at https://Mytour/ham-index-trong-excel/
Syntax for the Match function
= =MATCH(lookup_value,lookup_array,match_type)
Explore additional usage of the Match function at https://Mytour/ham-match-ham-tim-kiem-mot-gia-tri-xac-dinh-trong-mot-mang-pham-vi-o-trong-excel/
Similarly, with the given example, you can use the INDEX function in combination with the MATCH function to search with multiple conditions.
- Enter the function =INDEX(C6:E12;MATCH(C15&D15;C6:C12&D6:D12;0);3)
- Press the combination Ctrl + Shift + Enter to convert it into an array formula, curly braces {} will appear around the formula.
The MATCH function returns the row position containing the condition in the data table with:
- C15&D15 as the two search values.
- C6:C12&D6:D12 as the two columns containing the search values (search array).
- 0 as the exact match type.
The INDEX function returns the value in the 3rd column, the row being the value returned by the MATCH function.
- C6:E12 represents the range of cells you need to search and return results.
- The MATCH function is the row index from which a value is returned.
- 3 is the column index from which a value is returned.
This article guides you through two methods to search for data satisfying two conditions in Excel. For more complex conditions, you can apply similar approaches. We hope this article proves helpful to you. Best of luck!
