求 Excel Formula

向 sheet 2, A1 個 cell 度用

=IF(Sheet1!A1="England",Sheet1!A1," ")

之後 copy 個 formula 去唔同 cell 度~~
可能唔得~~係因為個 cell 個 format 唔岩~~
你將個 cell 個 format 轉番做 Text ( 文字)
之後向 sheet 1 有 England 個 cell 度 按 " F2 " 之後再 " Enter "
咁就 ok 啦~~

Assume your sheet 1 cell A1 is CHINA ENGLAND US (as you have shown in your question) and you want sheet 2 cell A1 to show the same value (since it contains ENGLAND).

Go to sheet 2 cell A1, type in the following formula:
=IF(ISNUMBER(SEARCH("ENGLAND",Sheet1!A1)),Sheet1!A1,"")

If this is what you want, then do the same for other cells...

不要少看此題難度, 如不用輔助格, 是牽涉相當多的函數知識
Sheet2的A1輸入以下的陣列公式:
=INDEX(Sheet1!A:A,SMALL(IF(ISNUMBER(FIND("England",Sheet1!$A$1:$A$100&Sheet1!$B$1:$B$100&Sheet1!$C$1:$C$100)),ROW($1:$100),65536),ROW(A1)))&""
陣列公式的輸入方法是, 輸入公式後, 不要按Enter, 要用左手先按Ctrl + Shift兩鍵不放, 右手才按Enter
輸入陣列公式後, 再將公式右拉, 下拉
Sheet2的 ROW 1, 2 & 3 會顯示Sheet1的ROW 1, 2 & 5
如Sheet1的內容更改或不斷向下新增資料, Sheet2的內容都會自動更新
注意: 如 欲ENGLAND, England, england被視為相同, 公式的FIND 便要改成 SEARCH

try this:

=IF(ISERROR(FIND("England",Sheet1!A1)),"",Sheet1!A1)

公式往下拉便成

2008-07-10 00:39:24 補充：
sorry, 睇錯題目
公式可勉強改為: =IF(ISERROR(FIND("England",Sheet1!$A1&$B1&C1)),"",Sheet1!$A1)
(公式往右拉及下拉)

但, 始終遠不如樓下新田美香的全面......佩服