就是．梅酒

在Excel中最常用的查表功能是Vlookup、Hlookup，這是最直覺拿來套用就可以的了！不過他們有個限制，資料需要經過排序，如果沒排序的話，函數搜尋出來的資料會出現錯誤。在這裡就不探討這兩個函數了！
其實我最喜歡用INDEX和MATCH函數來達到相似的功能，好處是不需要排序，比Vlookup、Hlookup好這麼一個功能。不過MATCH函數也有缺點的啦，就是他找MATCH資料的話，資料要是唯一的！！這裡不懂沒關係，我先講起來放而已！（不然我真不知道怎麼起頭這篇文章）

假如有一筆資料如下圖：



需求的功能

先借《超級星光大道》的資料來舉個例，假設我想要查10/12號比賽當天，唱真命天子的演唱者是誰；或是10/6號唱心動的人是誰。這樣的查表模式。
這樣我們就需要有兩個選項嘛！？先選擇日期，在選擇歌曲，然後得到結果 － 演唱者。
這兩個選項，我打算用下拉式選單來做，這樣就不用key得要死啦！
這裡很妙的地方在於，當你選擇完日期之後，選擇歌曲的該儲存格會跑出相對應的歌曲，也就是選擇了10/12日(C1)，下面的歌曲就只會讓你選擇C2到C13（C2:C13）；選擇10/6日(B1)後，歌曲的選擇就只能選擇B2到B13（B2:B13）。

會用到的函數
要做到這樣的功能，需要用到以下幾個函數：

1. 下拉式選單
2. COUNTA()
3. MATCH()
4. OFFSET()
5. INDEX()

Example File

強烈建議將檔案下載回去看，邊看檔案邊看說明，比較好懂。（因為我對自己的表達能力沒太大的信心）

函數解析的步驟 － 從裡到外。
假設有兩個函數分別為A()與B()，而B函數被A函數包起來，A(B())，Excel會先把B函數算出來後的結果再帶入A中。這跟數學的算法是一樣的，有括號先算，然後先乘除後加減。


下拉式選單
Excel中有兩種下拉式選單，在這裡先只用一種。
移到你要作成下拉式選單的儲存格，接著在最上面的選單「資料」→「驗證」
跳出對話框
在「儲存格內允許(A)」的項目選擇「清單」。在「來源(S)」可以填寫選項範圍，這裡我先填B1到C1！選單就會出現B1到C1的內容讓你選了！
「來源(S)」除了可以填儲存格的位置之外，還可以填入公式！下一格F18就是要填公式囉！


COUNTA(範圍)
功用是：計算出範圍內有多少的非空白儲存格的數目。
COUNTA(1:1)
就是計算第一列有幾個非空白儲存格。在這裡會得到COUNTA(1:1)=3，第一列只有三欄資料。

為甚麼要這樣做呢？
因為這個table可能會一直增加下去，《超級星光大道》每個禮拜都會比賽嘛，這個table每過一個禮拜資料就會往右邊延伸下去。所以用COUNTA來算總共已經有幾欄資料了。


MATCH(要搜尋的值,要搜尋的範圍,type) 的用法，
功用是：在指定範圍內找到指定的值，標出該值在範圍內的位置
type=0 是 精確相等 在「搜尋範圍」內找到完全符合「要搜尋的值」的位置；
type=-1 or 1 是 相似值 只要搜尋範圍內有相似於「要搜尋的值」就指出他的位置。
我通常都是type用0。

第一個下拉式選單選日期嘛！我們用MATCH去找到這個日期是在表格的第幾欄，MATCH(F17,1:1,0)。
在1:1的範圍（即第一列）找到F17格的值，。F17那格就是你選擇的比賽日期。
如果是
「10月6日比賽」則MATCH函數得到的結果是2 － 「10月6日比賽」這個值在第一列裡是第二欄；
「10月12日比賽」，MATCH得到結果是3 － 「10月12日比賽」這個值在第一列裡是第三欄。


OFFSET(範圍,起始點列數,起始點欄數,資料高度,資料寬度)
OFFSET()的結果，會是一個「資料範圍」。
範圍Reference：我們圈選A1:C13，就是歌曲的列表，
起始列數Rows：1，從第二列開始，第二列才是歌曲的曲目。
起始欄數Cols：MATCH(F17,1:1,0)-1。MATCH來幫我們定位比賽日期是第幾欄嘛！
資料高度Height：COUNTA(A:A)-1，A欄的資料高度COUNTA(A:A)=13，可是要扣掉第一列是標題列，所以是13-1=12列。其實不一定要用A欄定義資料的高度啦，你也可以用B欄、C欄，我取A欄是因為，A欄的資料一定都在！
資料寬度Width：1。只要一欄就好。

=OFFSET(A1:C13,1,MATCH(F17,1:1,0)-1,COUNTA(A:A)-1,1)
如果
日期是「10月12日比賽」

F18這格就填寫清單成這樣。


會用了OFFSET以後，你自己就可以把F17格的「選日期」的清單改成這樣
=OFFSET(1:1,0,1,1,COUNTA(1:1)-1)
如此一來，當每個禮拜都比賽的《超級星光大道》每個禮拜都會新增加資料進去，「10月6日比賽」、「10月12日比賽」、「10月18日比賽」………一直下去。你的F17格就增加比賽日期的選項！！！


INDEX(範圍,第幾列,第幾欄)
在指定的範圍裡面的某一欄某一列的儲存格值。
在F19格，我們要得出結果，F17、F18兩個選項選完之後，我要讓這格自動出現演唱者是誰！
範圍：A2:C13
第幾列：MATCH(F18,OFFSET(A1:C13,1,MATCH(F17,1:1,0)-1,COUNTA(A:A)-1,1),0)。別被這麼長的函數嚇到了，先把灰色的不看，其實他是個很單純的MATCH函數。灰色的部份是MATCH的範圍，而這個〝範圍〞跟F18中定義的範圍是一樣的，所以我直接copy&paste貼到這裡來。比如說，你選擇的「10月12日比賽」＋「火」，應該要得到演唱者是「林佩瑤」嘛！「火」是「10月12日比賽」欄中的第3列，所以MATCH得到的值是3。
第幾欄：1。
這一格最重要的是第幾列這個參數。


大功告成。