有兩個表...Sheet A 同 B
A入面有好多資料, 其中行A係性別, 行B係名同B
Sheet B 入面都係一樣, 行A係空白既性別, 行B係名
但D 名既次序, 係兩張Sheet 都唔一樣, 所以, 我就係想Sheet B 入面空白性別果行....if sheet B 入面行B個名, 係對到Sheet A行B 既名係一樣的話, 就將Sheet A既性別, 填去Sheet B 既行A空白性別果行到
Sheet A:
.. A B
1 M Alison
2 F Peter
3 F Candy
Sheet B:
..A B
1 . Candy
2 . Alison
3 . Peter
咁應該係Sheet B既A1果行寫 Vlookup (X, X, X, X)
有關Excel Vlookup 問題
2014-10-28 9:07 pm
回答 (2)
2014-11-01 6:33 pm
✔ 最佳答案
公式如下：A1: =IF(ISNA(MATCH(B1,'Sheet A'!B:B,FALSE)),"Not_in_list",INDEX('Sheet A'!A:B,MATCH(B1,'Sheet A'!B:B,FALSE),1))
如果不要求美觀的話可用下面這個(找不到會出N/A)：
A1: =INDEX('Sheet A'!A:B,MATCH(B1,'Sheet A'!B:B,FALSE),1)
2014-10-31 8:08 pm
By default Excel worksheets are named as Sheet1, Sheet2, Sheet3, ...
So, I use Sheet1 and Sheet2 in the formula instead of SheetA, SheetB.
One thing to note is that vlookup requires the referenced value to be resided on a column by the right of the lookup value, but this is reversed on the Sheet1 list. This made vlookup unable to function, a workaround is to use the combination of two functions MATCH and INDEX. MATCH can lookup value from an array and return the position of the lookup value found, INDEX can return the value of the indexed item of an array. The formula to be put on Sheet2 cell A1 is:
=INDEX(Sheet1!A$1:A$3,MATCH(B1,Sheet1!B$1:B$3,0))
This formula can be copied down the column A.
So, I use Sheet1 and Sheet2 in the formula instead of SheetA, SheetB.
One thing to note is that vlookup requires the referenced value to be resided on a column by the right of the lookup value, but this is reversed on the Sheet1 list. This made vlookup unable to function, a workaround is to use the combination of two functions MATCH and INDEX. MATCH can lookup value from an array and return the position of the lookup value found, INDEX can return the value of the indexed item of an array. The formula to be put on Sheet2 cell A1 is:
=INDEX(Sheet1!A$1:A$3,MATCH(B1,Sheet1!B$1:B$3,0))
This formula can be copied down the column A.
收錄日期: 2021-04-12 00:07:58
原文連結 [永久失效]:
https://hk.answers.yahoo.com/question/index?qid=20141028000051KK00036