[發問] match 或 lookup 函數功能問題

ricky8751

各位好, 我在使用match 或 lookup 函數時發現有問題: Book1.zip (6.89 KB)

下載次數: 18

2014-7-23 11:38

其實我只想用兩函數找符合指定數值那欄最上面的標題值, 如找尋範圍裡有其他數值大過指定要求時(即黃色單元格), 函數會出現錯誤, 想請教各位原因或有沒有其他函數可代替使可達到我想要的結果. 先謝謝各位幫忙!

p212

回復 1# ricky8751
儲存格L12輸入陣列公式
=INDEX($B$1:$K$1,,SMALL(IF($B12:$K12=30,COLUMN($B12:$K12)-1,FALSE),1))
向下複製
請參考！

p212

回復 1# ricky8751
原附檔之儲存格L12公式建議修改為
=OFFSET($A$1,0,MATCH(30,B12:K12,0),,)
請參考！

p212

回復 1# ricky8751
原附檔之儲存格L34公式=LOOKUP(30,B34:K34,$B$1:$K$1)
會不會是因為「lookup_vector 中的值必須以遞增順序排列：...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE；否則，LOOKUP 函數可能不會傳回正確的值。」而致#N/A的結果？
請參考！

ML089

回復 1# ricky8751

LOOKUP及MATCH用法如下

    L2 =OFFSET($A$1,0,MATCH(30,B2:K2,))
L22 =LOOKUP(,0/(B22:K22=30),$B$1:$K$1)

下拉複製

p212

回復 5# ML089
可否煩請ML089版主賜教？
1、L2 =OFFSET($A$1,0,MATCH(30,B2:K2,))
(1)其match_type「省略」和輸入「1」是一樣的，但為何在1#的範例檔會呈現＃N/A？
(2)您對match_type為何不用「0」？（1#不是用MATCH指定尋找「30」）
2、L22 =LOOKUP(,0/(B22:K22=30),$B$1:$K$1)
(1)其lookup_value為何可省略？省略的值表示為「0」？
(2)在此LOOKUP得到的陣列是什麼？
謝謝！

ML089

回復 6# p212
>>可否煩請ML089版主賜教？
>>1、L2 =OFFSET($A$1,0,MATCH(30,B2:K2,))
>>(1)其match_type「省略」和輸入「1」是一樣的，但為何在1#的範例檔會呈現＃N/A？
>>(2)您對match_type為何不用「0」？（1#不是用MATCH指定尋找「30」）

回覆:
(1)
如果 match_type 是 1，則 MATCH 函數會找到等於或僅次於 lookup_value 的值。Lookup_array 必須以遞增次序排列：...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE。
原公式中因Lookup_array沒有遞增次序排列所以錯誤
(2)
=OFFSET($A$1,0,MATCH(30,B2:K2)) 這樣match_type是省略為 1
=OFFSET($A$1,0,MATCH(30,B2:K2,))這樣match_type是要輸入沒有輸入為 0 (這是簡化輸入的壞習慣，請見諒)


>>2、L22 =LOOKUP(,0/(B22:K22=30),$B$1:$K$1)
>>(1)其lookup_value為何可省略？省略的值表示為「0」？
>>(2)在此LOOKUP得到的陣列是什麼？

回覆:
(1)
L22 =LOOKUP(,0/(B22:K22=30),$B$1:$K$1)
lookup_value並沒有省略只是要輸入沒有輸入時為 0 (這是簡化輸入的壞習慣，請見諒)
(2)
B34:K34 = {100,100,30,100,100,100,100,100,100,100}
0/(B34:K34=30) = {#DIV/0!,#DIV/0!,0,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!}
L34 =LOOKUP(,0/(B34:K34=30),$B$1:$K$1) = 3
LOOKUP具有容錯能力，雖然 Lookup_vector中有#DIV/0!錯誤資料時並不會中斷仍會繼續進行作業，符合時 0/TRUE = 0，lookup_value 用  0可找符合條件任一筆(一般用於唯一符合條件)，用 1可找符合條件最後一筆。

ricky8751

回復 7# ML089

感謝版主ML089 及 p212 詳盡解答, 尤其這應用 0/(B34:K34=30) = {#DIV/0!,#DIV/0!,0,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!}
雖暫未能完全理解, 但已可解決我想要的問題了, 謝謝~

p212

回復 7# ML089
哈哈！年近50老花未見MATCH(30,B2:K2)與MATCH(30,B2:K2,)的差別，請見諒！
非常感謝ML089版大熱心的解說，傳授對「非排序」資料之模糊查尋使用LOOKUP(0,0/條件,查尋區域)的觀念與公式簡約化的技巧。