返回列表 上一主題 發帖

[發問] 請教VLOOKUP中被查詢範圍如何加入選字數函數

Bodhidharma

帖子
186 
主題
6 
精華
0 
積分
218 
點名
0  
作業系統
win7 
軟體版本
office 2007 
閱讀權限
20 
性別
男 
註冊時間
2012-11-12 
最後登錄
2014-4-15 
1# 跳轉到 »
發表於 2013-5-13 12:46 | 顯示全部帖子
回復 1# 97forum

用left太限縮了，可考慮使用search函數
陣列公式
  1. =INDEX($D$1:$D$4,MATCH(1,SEARCH($A$1,$C$1:$C$4),0))
複製代碼
會回傳D欄中，第一個函有A1的值
如果search出來符合的值不只一個(比方說A1="大甲")
  1. =INDEX($C$1:$C$4,SMALL(IF(ISNUMBER(SEARCH($A$1,$C$1:$C$4)),ROW($C$1:$C$4),""),ROW(A1)))
  2. =INDEX($D$1:$D$4,SMALL(IF(ISNUMBER(SEARCH($A$1,$C$1:$C$4)),ROW($C$1:$C$4),""),ROW(A1)))
複製代碼
下拉，就會顯示所有符合的公司名稱以及值

TOP

Bodhidharma

帖子
186 
主題
6 
精華
0 
積分
218 
點名
0  
作業系統
win7 
軟體版本
office 2007 
閱讀權限
20 
性別
男 
註冊時間
2012-11-12 
最後登錄
2014-4-15 
2#
發表於 2013-5-13 12:47 | 顯示全部帖子
回復 3# 97forum

FLASE 改成FALSE試試

TOP

Bodhidharma

帖子
186 
主題
6 
精華
0 
積分
218 
點名
0  
作業系統
win7 
軟體版本
office 2007 
閱讀權限
20 
性別
男 
註冊時間
2012-11-12 
最後登錄
2014-4-15 
3#
發表於 2013-5-13 15:41 | 顯示全部帖子
回復 7# 97forum

表格也會有表格的名稱可以使用，應該不會無法使用我的公式
請參考看看以下檔案
搜尋表格.rar (7.64 KB)

TOP

Bodhidharma

帖子
186 
主題
6 
精華
0 
積分
218 
點名
0  
作業系統
win7 
軟體版本
office 2007 
閱讀權限
20 
性別
男 
註冊時間
2012-11-12 
最後登錄
2014-4-15 
4#
發表於 2013-5-14 15:49 | 顯示全部帖子
本帖最後由 Bodhidharma 於 2013-5-14 15:50 編輯

回復 11# 97forum

基本上就只是我原本公式改個名稱
  1. =INDEX($D$1:$D$4,MATCH(1,SEARCH($A$1,$C$1:$C$4),0))
複製代碼
B2陣列公式
  1. =INDEX(表格__2003sv_CHIComp01_comCustomer[ID],MATCH(1,SEARCH(A2,表格__2003sv_CHIComp01_comCustomer[InvoiceHead]),0))
複製代碼
TEST-VLOOKUP_sol.rar (12.02 KB)

TOP

Bodhidharma

帖子
186 
主題
6 
精華
0 
積分
218 
點名
0  
作業系統
win7 
軟體版本
office 2007 
閱讀權限
20 
性別
男 
註冊時間
2012-11-12 
最後登錄
2014-4-15 
5#
發表於 2013-5-14 16:01 | 顯示全部帖子
回復 12# Bodhidharma

抱歉，我公式裡應該再加個isnumber，不然只有名稱出現在最前面才會搜尋的到
B2陣列公式
  1. =INDEX(表格__2003sv_CHIComp01_comCustomer[ID],MATCH(TRUE,ISNUMBER(SEARCH(A2,表格__2003sv_CHIComp01_comCustomer[InvoiceHead])),0))
複製代碼
TEST-VLOOKUP_sol.rar (12.04 KB)

TOP

Bodhidharma

帖子
186 
主題
6 
精華
0 
積分
218 
點名
0  
作業系統
win7 
軟體版本
office 2007 
閱讀權限
20 
性別
男 
註冊時間
2012-11-12 
最後登錄
2014-4-15 
6#
發表於 2013-5-14 19:03 | 顯示全部帖子
本帖最後由 Bodhidharma 於 2013-5-14 19:05 編輯

回復 14# 97forum

剛剛找到一篇不錯的說明，(關鍵字："結構化參照")
http://office.microsoft.com/zh-tw/excel-help/HA010342999.aspx#BMexamples_of_using_structured_referenc
才發現我的公式有一點概念上的問題
  1. =INDEX(表格__2003sv_CHIComp01_comCustomer[ID],MATCH(TRUE,ISNUMBER(SEARCH(A2,表格__2003sv_CHIComp01_comCustomer[InvoiceHead])),0))
複製代碼
之中有使用到"A2"，其實在表格中不應該出現這樣的東西，從該篇說明可以知道應該使用"表格5[[#這個列],[中文師名]]"，才不會因為位置而造成公式有非預期的效果
因此整個公式應該是陣列公式：
  1. =INDEX(表格__2003sv_CHIComp01_comCustomer[ID],MATCH(TRUE,ISNUMBER(SEARCH(表格5[[#這個列],[中文師名]],表格__2003sv_CHIComp01_comCustomer[InvoiceHead])),0))
複製代碼
可以發現整列的公式都一樣，與所在的列數無關
TEST-VLOOKUP_sol.rar (11.91 KB)

note：另外match函數中一定要使用true，不能用1，因為後面isnumber回傳的會是邏輯值(true,false)而非數字，所以如果用1，後面的邏輯值又沒有轉換為數字的話就會找不到

TOP

Bodhidharma

帖子
186 
主題
6 
精華
0 
積分
218 
點名
0  
作業系統
win7 
軟體版本
office 2007 
閱讀權限
20 
性別
男 
註冊時間
2012-11-12 
最後登錄
2014-4-15 
7#
發表於 2013-5-15 14:09 | 顯示全部帖子
回復 16# 准提部林

原來vlookup可以使用萬用字元，受教！
所以原po檔案的原始公式
  1. =VLOOKUP(LEFT(表格5[[#這個列],[中文師名]],6),表格__2003sv_CHIComp01_comCustomer[[#全部],[InvoiceHead]:[ID]],2,0)
複製代碼
只要稍微修改為
  1. =VLOOKUP("*"&表格5[[#這個列],[中文師名]]&"*",表格__2003sv_CHIComp01_comCustomer[[#全部],[InvoiceHead]:[ID]],2,0)
複製代碼
即可達成和我公式相同的效果

至於=LOOKUP(9^9,FIND(A1,D1:D4),E1:E4)
當有不只一個符合時，會回傳「符合的字串是在最後面的」符合項目，感覺有點隨機

TOP

        靜思自在 : 靜坐常恩己過、閒談莫論人非。
返回列表 上一主題