The vector form of LOOKUP looks in a one-row or one-column range (known as a vector) for a value, and then returns a value from the same position in a second one-row or one-column range. Use this form of the LOOKUP function when you want to specify the range that contains the values that you want to match.
Syntax for Vector Form
LOOKUP(lookup_value,lookup_vector,result_vector)
| ? |
Lookup_value is a value that LOOKUP searches for in the first vector. Lookup_value can be a number, text, a logical value, or a name or reference that refers to a value. |
| ? |
Lookup_vector is a range that contains only one row or one column. The values in lookup_vector can be text, numbers, or logical values.
Important The values in lookup_vector must be placed in ascending order. For example, -2, -1, 0, 1, 2 or A-Z or FALSE, TRUE. If you do not do so, LOOKUP may not give the correct value. Uppercase and lowercase text are equivalent. |
| ? |
Result_vector is a range that contains only one row or column. It must be the same size as lookup_vector. |
Note:
1:当lookup_vector是升序排列且有重复值时，lookup函数是定位在小于等于查找值的最后一个。eg:=lookup(3,{1;1;2;2;2},{1;2;3;4;5})结果是5
2.lookup_vector内的数据类型只有4种，数字,文本，逻辑值，错误值。当查找值分别为数字，文本，逻辑值时，只查找lookup_vector内的同类型的数据，其它类型的忽略。
eg:=LOOKUP(1,{1;#DIV/0!;"1";2;FALSE},{1;2;3;4;5})结果为1
3.当lookup_value比lookup_vector内的所有同类型数据都大时，lookup函数定位在该同类型数据的最后一个。
eg:=LOOKUP(100,{#DIV/0!;99;73;89;6;TRUE;"我";25;28;"0";FALSE;9;"79"})结果是9
4.lookup函数可以返回一维二维的内存数组。
eg:=LOOKUP({5;6;7;8;9},{1;2;3;4;5;6;7;8;9;10},{"A";"B";"C";"D";"E";"X";"C";"E";"L"})结果为
{"E";"X";"C";"E";"L"}
eg:=LOOKUP(ROW(1:5)+COLUMN(A1:C1),{1;2;3;4;5;6;7;8;9;10}{"A";"B";"C";"D";"E";"X";"C";"E";"L"})结果为二维内存数组
5.lookup函数在数组连接中的作用
eg:将{"A";"B";"C"},{"D";"E";"F";"G"}连接成{"A";"B";"C";"D";"E";"F";"G"}
=IF(ROW(1:7)<4,{"A";"B";"C"},LOOKUP(ROW(1:7),ROW(4:7),{"D";"E";"F";"G"}))
6.求汉字的拼音声母(非常局现性的一个办法，只适用于一些常用字，太生僻的会出错)，我只试过03版本，07下行不通。
=LOOKUP(CODE(A1),45217+{0,36,544,1101,1609,1793,2080,2397,2902,3845,4107,4679,5154,5397,5405,5689,6170,6229,7001,7481,7763,8472,9264},{"A","B","C","D","E","F","G","H","J","K","L","M","N","O","P","Q","R","S","T","W","X","Y","Z"})
6.未完待续....
