Document directory
-
- Vector LOOKUP
-
- LOOKUP in array form
Today, my sister asked me how to use lookup. I found this article on the Internet. It is still concise and concise, and I have posted it here after studying it.I personally think the two examples are helpful.-CTH Overview This article introduces how to use
LOOKUPFunction. More information
LOOKUPThe function returns a value in a row, column, or array.LOOKUPA function has two syntax forms: vector and array. Vector FormLOOKUPSearch for values in one row or column (called a vector) and return the values at the same position in the other row or column. Array formatLOOKUPFind the specified value in the first row or column of the array, and return the value at the same position in the last row or column of the array.
Vector LOOKUP
Vector FormLOOKUPSearch for values in one row or column (called a vector) and return the values at the same position in the other row or column. If you want to specify a region that contains the value to be matched, use this formatLOOKUPFunction.
Vector syntax
LOOKUP (Lookup_value,Lookup_vector,Result_vector)
• |
Lookup_valueYesLookupThe value found in the first vector.Lookup_valueIt can be a number, text, logical value, or a name or reference representing a value. |
• |
Lookup_vectorIs a region that contains only one row or one column.Lookup_vectorThe value can be a text, number, or logical value. Important:Lookup_vectorValues must be listed in ascending order. For example,-2,-1, 0, 1, 2 or A-Z or false, true. Otherwise,LOOKUPThe returned value may be incorrect. Uppercase and lowercase texts are equivalent. |
• |
Result_vectorIs a region that contains only one row or one column. It must be in the same sizeLookup_vectorSame. |
Note:
• |
IfLookupNot foundLookup_value, It will matchLookup_vectorSmaller than or equalLookup_value. |
• |
IfLookup_valueLessLookup_vectorThe minimum valueLOOKUP# N/A error is returned. |
Example
|
A |
B |
1 |
Frequency |
Color |
2 |
4.14 |
Red |
3 |
4.19 |
Orange |
4 |
5.17 |
Yellow |
5 |
5.77 |
Green |
6 |
6.39 |
Blue |
Formula |
Description (result) |
= Lookup (4.91, A2: A6, B2: B6) |
Search for 4.19 In column A and return the value (orange) in the same row of Column B ). |
= Lookup (5.00, A2: A6, B2: B6) |
Search for 5.00 in column A and return the value (orange) in the same row of Column B ). |
= Lookup (7.66, A2: A6, B2: B6) |
Search for 7.66 in column A, match the small value (6.39), and then return the value (blue) in the same row of Column B ). |
= Lookup (0, A2: A6, B2: B6) |
Find 0 in column A and return an error because 0 is smaller than the minimum value (# N/A) in lookup_vector A2: A7 ). |
LOOKUP in array form
Array formatLOOKUPFind the specified value in the first row or column of the array, and return the value at the same position in the last row or column of the array. If the value to be matched is in the first row or column of the array, useLOOKUP.
Array syntax
LOOKUP (Lookup_value,Array)
• |
Lookup_valueYesLOOKUPThe value searched in the array.Lookup_valueIt can be a number, text, logical value, or a name or reference representing a value.
• |
IfLOOKUPNot foundLookup_valueIt will use the array smaller than or equalLookup_value. |
• |
IfLookup_valueIf the value is smaller than the minimum value in the first row or column (depending on the array dimension ),LOOKUP# N/A error is returned. |
|
• |
ArrayIs a cell area, which containsLookup_valueThe text, number, or logical value to be compared. Array formatLOOKUPAndHLOOKUPFunctions andVLOOKUPFunctions are similar. The difference is thatHLOOKUPSearch in the first lineLookup_value,VLOOKUPSearch in the first column, andLOOKUPSearch by array dimension.
• |
IfArrayIf the width of the covered area is greater than the height (more columns than rows ),LOOKUPWill search in the first lineLookup_value. |
• |
IfArrayIf the covered area is a square or the height is greater than the width (rows and columns ),LOOKUPSearch in the first column. |
• |
UseHLOOKUPAndVLOOKUPYou can perform down or cross-indexing,LOOKUPAlways select the last value in the row or column. |
Important:ArrayValues must be listed in ascending order. For example,-2,-1, 0, 1, 2 or A-Z or FALSE, TRUE. Otherwise,LOOKUPThe returned value may be incorrect. Uppercase and lowercase texts are equivalent. |
Example
|
A |
B |
1 |
A |
1 |
2 |
B |
2 |
3 |
C |
3 |
4 |
D |
4 |
Formula |
Description (result) |
= Lookup ("C", A1: B4) |
Search for "c" in the first row of the array, and return the value (3) in the same column of the last row ). |
= Lookup ("bump", A1: B4) |
Search for "bump" in the first row of the array, and return the value (2) in the last column of the same row ). |