2016年6月6日 星期一

Excel-以欄列交叉對照查表(OFFSET,MATCH)

網友問到一個 Excel 問題:根據先前文章 Excel-以欄列交叉對照查表(OFFSET,MATCH),參考下圖是由欄名和對應數值要反推列名。
Excel-以欄列交叉對照查表(OFFSET,MATCH)
如果想要由列名和對應數值要反推欄名,該如何處理?
Excel-以欄列交叉對照查表(OFFSET,MATCH)

【公式設計與解析】
儲存格M5:
=OFFSET(A1,MATCH(M2,OFFSET(A2,,MATCH(M1,B1:K1,0),12,1),0),0)
這個公式利用了二組 MATCH 函數和 OFFSET 函數。
(1) MATCH(M1,B1:K1,0),12,1)
求得儲存格M1在儲存格B1:K1中的位置,傳回一個數值(第幾個),本例傳回:5。
(2) OFFSET(A2,,MATCH(M1,B1:K1,0),12,1),0)
根據第(1)式傳回的數值『5』,由 OFFSET 函數查詢對應的儲存格內容(本例為『辰』)
(3) MATCH(M2,OFFSET(A2,,MATCH(M1,B1:K1,0),12,1),0)
求得儲存格M1(=44)在儲存格B1:K1中的位置,傳回一個數值(第幾個),本例傳回:4。
(4) OFFSET(A1,MATCH(M2,OFFSET(A2,,MATCH(M1,B1:K1,0),12,1),0),0)
根據第(1)式傳回的數值『4』,由 OFFSET 函數查詢對應的儲存格內容(本例為『丁』)

【補充資料】
你也以使用 INDEX 函數,儲存格M5:
=INDEX(A2:A13,MATCH(M2,OFFSET(A2,,MATCH(M1,B1:K1,0),12,1),0),1)

沒有留言:

張貼留言

好康東東