有關EXCEL IF 的問題請高手指導:
(SHEET1)
A B C
1 2 1
目的:
係(SHEET2)的(B4)上打1OR2都可以自己搵個NUMBER出黎:
IF((VLOOKUP(SHEET2!B4,SHEET1!A:A,1,0))=SHEET2!B4,
VLOOKUP(SHEET2!B4,SHEET1!A:B,2,0),
VLOOKUP(SHEET2!B4,SHEET1!B:C,2,0))
問題係當我用1去搵2就得
但用2去搵1就出#N/A...
請賜教~
有關EXCEL IF的問題
2011-03-28 7:45 pm
回答 (3)
2011-03-29 12:20 am
✔ 最佳答案
你條 formula 相等於咁:=IF(符合情況,答案1,答案2)
而你應該咁改:
=IF(ISNA(符合情況),答案2,答案1)
你嘅 logic 定得好奇怪, 呢條 formula 只適合上述列明情況。
2011-03-29 13:22:12 補充：
應該冇關係.....
2011-03-29 8:41 pm
3Q回覆
如果上面的1,2,1 唔係value係 text的話
一定要用method 1先得?
如果上面的1,2,1 唔係value係 text的話
一定要用method 1先得?
2011-03-29 5:23 am
method 1:
=IF(ISNA(VLOOKUP(Sheet2!B4,Sheet1!A:A,1,0)),VLOOKUP(Sheet2!B4,Sheet1!B:C,2,0),VLOOKUP(Sheet2!B4,Sheet1!A:B,2,0))
method 2:
=IF(COUNTIF(Sheet1!A:A,Sheet2!B4)>=1,VLOOKUP(Sheet2!B4,Sheet1!A:B,2,0),VLOOKUP(Sheet2!B4,Sheet1!B:C,2,0))
=IF(ISNA(VLOOKUP(Sheet2!B4,Sheet1!A:A,1,0)),VLOOKUP(Sheet2!B4,Sheet1!B:C,2,0),VLOOKUP(Sheet2!B4,Sheet1!A:B,2,0))
method 2:
=IF(COUNTIF(Sheet1!A:A,Sheet2!B4)>=1,VLOOKUP(Sheet2!B4,Sheet1!A:B,2,0),VLOOKUP(Sheet2!B4,Sheet1!B:C,2,0))
收錄日期: 2021-04-23 23:31:24
原文連結 [永久失效]:
https://hk.answers.yahoo.com/question/index?qid=20110328000051KK00328