有網友根據上一篇文章：Excel-多條件的查詢(INDEX+MATCH+陣列) http://isvincent.blogspot.com/2012/01/excel-indexmatch.html
問到如果要查詢的資料不止一筆時，公式該如何寫？參考下圖，要由外形和尺寸這兩個條件來找符合的零件編號及其售價。
【準備工作】
1. 選取儲存格A1:D19，按一下 Ctrl+Shift+F3 鍵，建立名稱：零件編號、外形、尺寸。
2. 選取儲存格A2:D19，建立名稱：資料。
【公式說明】
儲存格F4：{=IFERROR(INDEX(資料,SMALL(IF((外形=$G$1)*(尺寸=$G$2),ROW($1:$18)),ROW(1:1)),1),"")}
這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵。
(外形=$G$1)*(尺寸=$G$2)：公式中的「*」乃將二個條件做 AND 運算，條件完全成立時會傳回 True ，反之傳回 False。
IF((外形=$G$1)*(尺寸=$G$2),ROW($2:$19))：因為資料共有 18 筆，所以將上式合乎條件者，傳回對應的列號(1~18)。
SMALL(IF((外形=$G$1)*(尺寸=$G$2),ROW($1:$18)),ROW(1:1)),1)：因為ROW(1:1)=1，所以可以取出合於條件者的最小的列號，當往下複製時產生ROW(2:2)=2，會取出合於條件者的第 2 小的列號，以此類推。
透過 INDEX 函數，將此式求得的列號來查詢第 1 欄中的資料。由於往下複製公式時，可能因為找不到資料而傳回錯誤值，所以利用 IFERROR 函數，將錯誤訊息轉換為空字串。
同理，
儲存格G4：{=IFERROR(INDEX(資料,SMALL(IF((外形=$G$1)*(尺寸=$G$2),ROW($1:$18)),ROW(1:1)),4),"")}
透過 INDEX 函數，將此式結果來查詢第 4 欄的結果。
複製儲存格F4:G4，往下各列貼上。
【補充資料】
關於詳細函數說明，請參考微軟網站：
INDEX：http://office.microsoft.com/zh-tw/excel-help/HP010342608.aspx
|
INDEX：傳回表格或範圍內的某個值或值的參照。 |
|
語法：INDEX(array, row_num, [column_num]) Array：儲存格範圍或陣列常數。 Row_num：選取陣列中傳回值的列。 Column_num：選取陣列中傳回值的欄。 |
IFERROR：http://office.microsoft.com/zh-tw/excel-help/HA010342587.aspx
|
IFERROR：如果公式計算錯誤，會傳回指定的值；否則，會傳回公式的結果。 |
|
語法：IFERROR(value, value_if_error) value：檢查此引數是否有錯誤。 value_if_error：公式計算錯誤時要傳回的值。 使用 IFERROR 函數，可以捕捉並處理公式中的錯誤。會評估下列錯誤類型：#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!。 |
留言列表