Excel如何vlookup文字數值查找

資訊時代,只要涉及到分類,都會有編號和名稱的區別。編號是系統識別和數位儲存的唯一身份證號碼,一旦確認決定,無法修改。名稱如同穿在編號上的衣服,用意只是讓編號更加好看、更加容易理解,正如同衣服可以換件穿,名稱也能事後修改,編號並不因此受影響。由於編號是系統唯一識別碼,通常報表跑出來,欄位都會有編號,可是不一定有名稱,或者有時自行整理的資料只有編號,因為名稱能夠一目瞭然,像這個情形,如果能有編號相對應的名稱出來,報表將趨於完美。

最近管理上有需要,將成本分攤率的設置,由原來的系統直接計算,改成手工修改再匯入系統,資訊人員先匯出一個系統可讀取的Excel格式範本,內容是原本的分攤設置,我必須依照匯出來的格式修改,然後再給資訊匯入系統。期間遇到一個困難,範本的部門編號是文字格式,而為了方便設置分攤率,我想將部門名稱vlookup過來,無奈文字和數值的格式有問題,Excel抓取不到,以下分享我的解決方法:

一、首先,資訊匯出來的格式範本,有年月會科部門、還有成本中心和分攤率。

成本分攤設置

二、範本裡只有部門編號,要依照部門重新分配成本分攤率,為了作業方便,想把部門名稱帶出來,先整理一份所有部門的代碼名稱對照表。

部門代碼名稱對照表

三、像這種狀況,第一時間當然是搬出VLOOKUP大神,拉好公式,卻發現無效,明明部門編號相符,顯示的卻是此題無解:「#N/A」。

vlookup杯具啦

四、仔細查看,部門儲存格左上角有個綠三角形,將滑鼠點到儲存格,出現驚嘆號:「此儲存格內的數字其格式為文字或開頭為單引號」,可以再點一下「關於這個錯誤的說明」。

關於這個錯誤的說明

五、瞭解錯誤原因,才能接著修正。

將儲存成文字的數值轉換成數值

六、最直接方案是將文字改成數字,選取好範圍,點擊驚嘆號,整批「轉換成數字」。

轉換成數字

七、轉換成數字之後,VLOOKUP終於發揮戰鬥力,果然,文字格式在作怪!

vlookup成功啦

八、瞭解了病因,不但可以對症下藥,還能七十二變。由於檔案修改完要再匯入系統,不太好直接將原檔文字格式改為數值,而且選取範圍也是煩人一件事,所以在不修改格式前提下,懶人法是在VLOOKUP套個VALUE函數,如此一來噹噹噹,查找成功!

在vlookup套用value函數

 

九、另外一個對策,既然參考值為文字格式,那乾脆把查找表格(部門代碼名稱對照表)也改成文字格式,於是輸入函數公式「=TEXT(A2,”@”)」。

TEXT函數運用

十、OK啦!

ok啦!