【懶人用 Excel】寫一次 VLOOKUP ,填滿全部資料格

可以寫一條 VLOOKUP,就打橫打直都填滿全部資料格嗎?而不用每一欄 Column Index 都設定嗎?

【懶人用 Excel】寫一次 VLOOKUP ,填滿全部資料格

可以寫一條 VLOOKUP,就打橫打直都填滿全部資料格嗎?而不用每一欄 Column Index 都設定嗎?

範本下載:https://www.dropbox.com/s/ak7faq7ib1j57eh/data%20lookup.xlsx?dl=0

假設你要輸入一個 ID(例如是會員編號),然後在「Data」一頁中 Lookup 相關的資料。

當然,這是一個很基本的 VLOOKUP 的做法。例如,在現在坐標 B2 的資料格中,應該做的是:=VLOOKUP(A2,Data!A:O, 2, FALSE)

這裏的意思,是以 A2 格的資料,傳回「Data」頁中第二欄的資料,即是「First Name」。C2 格則應該是傳回第三欄,D2 則是第四欄…… 如此類推。

=VLOOKUP(A2,Data!A:O, 3, FALSE)
=VLOOKUP(A2,Data!A:O, 4, FALSE)
=VLOOKUP(A2,Data!A:O, 5, FALSE)
……

如此類推。你留意到這個 Column Index 其實,也可以因着順序,而用 reference 吧?

所以,作為懶人的我,會起一張叫「_T」的頁面:

這個頁面只是簡單地由 1 開始數,但這其實可以用來做 Column Index 的參照:

原來:=VLOOKUP(A2,Data!A:O, 2, FALSE)
更改:=VLOOKUP($A2,Data!$A:$O,_T!B$1,FALSE)

這裏有兩個巧妙的地方。

第一就是 Column Index 改用了「_T!B1」,相對應於 B 欄的 Column Index,即傳回「2」。C 欄則會傳回「3」、D 欄傳回「4」,如此類推。這個懶人的做法,好處是如果你的 Data 本來就有很多欄的話,你就不用每一欄打 Column Index,減少錯誤。

另一個巧妙,就是要妙用 Absolute Reference。「_T!B$1」,即是將第一行「$1」變成 absolute,於是當你打橫拉按欄 auto-fill 時,它會隨着欄而轉 reference,但當你打直按行 auto-fill 時,則不會改變,只會參照「_T」的第一行。

而開首的「$A2」,則是 Absolute Reference 了 A 欄的 ID。於是,你的 formula 只需設定一次,就整張表橫拉豎拉 auto-fill 也可以了!假如你有 50 多欄,你也可以一下就搞定 50 多欄的 Column Index。

一條 formula 走天涯,是懶人的做法。但正經點說,是 consistent 的做法。只要你的 formula 設定正確,你不用怕其中一兩格或一兩欄的 formula 設定會因為拉 auto-fill 時拉錯,而令 reference 的資料錯誤。這種錯誤,其實很難偵測得到。

而且,如果你要 reference 的欄目需要更改,只需查看「_T」一頁更改就好,令你在設定欄目參照時,一目了然。

所以,身為一個懶人,我會想盡辦法,儘量將手上的工作,簡化成一條 formula。萬一你懷疑資料時,你只需要將 formula 重填一次,那就可以令你放心地相信你 Lookup 的資料的準確性。