324

VLOOKUP函數多行查找時複製公式的問題

示範檔

範例檔

324.XLSX

結果檔

VLOOKUP函數的第三個參數是查找傳回值所在的欄數,如果需要查找傳回多欄時,這個欄數值需要一個個的更改,比如傳回第2欄的,參數設置為2,如果需要傳回第3欄的,就需要把值改為3…如果有十幾欄會很麻煩的。那麼能不能讓第3個參數自動變呢?向後複製時自動變為2345。。。

Excel中有一個函數Column,它可以傳回指定儲存格的欄數,例如:=COLUMNS(A1) 返回值1=COLUMNS(B1) 返回值2

而儲存格參照複製時會自動發生變化,即A1隨公式向右複製時會變成B1C1D1。。這樣我們用COLUMN函數就可以轉換成數位1234。。。

下例我們依靠姓名同時查找部門及職稱。

image

步驟1:點取F2儲存格輸入公式:「=VLOOKUP($E2,$A$2:$C$16,COLUMN(B2),0)」後,再按「Enter」鍵。

步驟2:將滑鼠指標移到F2儲存格右下角,待滑鼠指標變為「+」號,拖曳滑鼠指標到G2儲存格,將公式複製到G2

步驟3:將滑鼠指標移到G2儲存格右下角,待滑鼠指標變為「+」號,拖曳滑鼠指標到G3儲存格,將公式複製到F3~G3。結果如上圖所示

公式說明:

這裡就是使用欄COLUMN(B2)轉化成可以自動遞增的數值,由於是用欄名來對應數值,故列號對此無影響,故B1~B1048576皆傳回的值都是2

 


arrow
arrow

    錦子老師 發表在 痞客邦 留言(0) 人氣()