Excel Vlookup IF 1 0 multiple conditions and Anatomy (four Examples)
We can combine Vlookup and IF function with multi-criteria to find in Excel, and can use an array as a condition for IF, which allows we to find multiple fields at the same time, such as finding clothing that is classified as a shirt and costs $8.8 in the Clothing Sales table.
Chapters:
0:00 Excel Vlookup If 1 0
0:59 Example 2: The condition is IF{0,1}
1:45 Example 3: The two columns are joined and found, with the condition IF{1,0}
2:43 Example 4: The two columns are joined and found, with the condition IF{0,1}
3:40 Anatomy of Example 1
4:30 Anatomy of Example 2
5:12 Anatomy of Example 3
Description:
Excel Vlookup IF 1 0 multiple conditions and Anatomy (four Examples)
One. Examples of Excel Vlookup If 1 0
Example 1: The condition is IF{1,0}
1. Suppose we want to find the name of the clothing that costs $8.8 from the clothing sales table. Enter the price we want to find in cell A11, for example 8.8, copy the formula =VLOOKUP(A11,IF({1,0},C2:C8,A2:A8),2,0) to cell B11, press Enter on the keyboard, then it will return "White cotton T-Shirt", the price of this shirt is exactly $8.8, indicating that the result is correct.
Note: If there are multiple $8.8 garments in the table, the Vlookup function will only return the first eligible garment.
2. Brief analysis of the formula (see below for detailed analysis)
The found ranges in the formula is the IF array condition, which consists of 1 and 0, 1 for True and 0 for False. When executing the formula, first take 1 from the array, and since 1 is true, return a value from C2:C8; Then take 0 from the array, and since 0 is false, return a value from A2:A8; This is repeated until C2 to C8 and A2 to A8 are traversed.
Example 2: The condition is IF{0,1}
1. Also want to find out the name of the clothing that costs $8.8 from the clothing sales table. Enter 8.8 in cell A12, copy the formula =VLOOKUP(A12,IF({0,1},A2:A8,C2:C8),2,0) to cell B12, press Enter on the keyboard, it will also return "White cotton T-Shirt", the result is the same as in example 1.
2. Brief analysis of the formula
Compared with Example 1, the array condition of IF just swaps 1 and 0, from {1,0} to {0,1}; When the formula is executed, 0 is taken from the array first, and since 0 is false, a value is returned from C2 to C8; Then take 1 from the array, and since 1 is true, it returns a value from A2 to A8; And so on until A2 to A8 and C2 to C8 are traversed. From the execution process, the order of the values is exactly the same as that of Example 1, so the same result is returned.
Example 3: The two columns are joined and found, with the condition IF{1,0} and the ranges joined with &
1. Suppose we want to find the name of the clothing with "Classification2" as "Shirt" and "Price" as $12 from the clothing sales table. Enter "Shirt" in A11 and 12 in B11, copy the formula =VLOOKUP(A11&B11,IF({1,0},C2:C8&D2:D8,A2:A8),2,0) to C11, press Ctrl + Shift + Enter on the keyboard, then the name of the clothing will be returned as "Pink long sleeve shirt".
2. Brief analysis of the formula
A11&B11 is the value to find, A11 and B11 are joined with the joined symbol & and the result is "Shirt12"; The condition is also the {1,0} array; When the formula is executed, 1 is taken first, and since 1 is true, a joined value is returned from C2:C8&D2:D8 (e.g., C2&D2); Then take 0, and since 0 is false, take a value from A2:A8; And so on until C2&D2 to C8&D8 and A2 to A8 are traversed .
Example 4: The two columns are joined and found, with the condition IF{0,1} and the ranges joined with &
1.Also want to find the name of the clothing with "Classification2" as "Shirt" and "Price" as $12 from the clothing sales table. Enter "Shirt" in A12 and 12 in B12.
2. Copy the formula =VLOOKUP(A12&B12,IF({0,1},A2:A8,C2:C8&D2:D8),2,0) to C12.
3. Press Ctrl + Shift + Enter on the keyboard, and the same clothing "Pink long sleeve shirt" as Example 3 will be returned.
4. Brief analysis of the formula
Compared with Example 3, Example 4 only changes the array condition of IF from {1,0} to {0,1}, and the true and false return values of the IF condition are swapped with each other, that is, the positions of C2:C8&D2:D8 and A2:A8 are swapped with each other. After this reversal, the value returned by the formula execution is the same as that of Example 3, so the same result can be found as in Example 3.
Two. Excel Vlookup function combined with IF{1,0} array analysis
Anatomy of Example 1
The formula is: =VLOOKUP(A11,IF({1,0},C2:C8,A2:A8),2,0)
1. The difference between horizontal array and vertical array in Excel.
A horizontal array is one that occupies two columns in a row, separated by "," between the elements, e.g. {1,0} is a horizontal array; The vertical array occupies a column and two rows, and the elements are separated by "; "separation, e.g. {1; 0} is a vertical array.
Смотрите видео Excel Vlookup IF 1 0 multiple conditions and Anatomy (four Examples) онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь Loinsure 07 Ноябрь 2023, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 112 раз и оно понравилось 1 людям.