英文:
Is there any formula to find cell reference from search function in Google sheets?
问题
OXFORD   BLACKWELL PUBL LTD   TRUE   BLACKWELL
MOSBY   ISRAEL MEDICAL ASSOC JOURNAL   TRUE   FALSE
CELL   PERGAMON-ELSEVIER SCIENCE LTD   TRUE   FALSE
AMER   PERGAMON-ELSEVIER SCIENCE LTD   TRUE   FALSE
BLACKWELL   MOSBY, INC   TRUE   MOBSY
   OXFORD UNIV PRESS   TRUE   OXFORD
   CELL PRESS   TRUE   CELL
   AMER COLL PHYSICIANS   TRUE   AMER
英文:
I have a range in column AH:AH19,
OXFORD
MOSBY
CELL
AMER
BLACKWELL
I want to use search function in column AI. It returns the value as true or false to column AJ when find something.
Column AI contains for example
BLACKWELL PUBL LTD
ISRAEL MEDICAL ASSOC JOURNAL
PERGAMON-ELSEVIER SCIENCE LTD 
PERGAMON-ELSEVIER SCIENCE LTD
MOSBY, INC
OXFORD UNIV PRESS
CELL PRESS
AMER COLL PHYSICIANS
So for example, each time the words OXFORD, MOBSY, CELL, AMER, BLACKWELL etc. any of one needed to search/find in column AI and returns the value in cell AJ as TRUE or FALSE.
I have build the formula for this as
=SUMPRODUCT(--ISNUMBER(SEARCH($AH$15:$AH$22,AI15)))>0
But they do not seem to work. If any suggestions please help to find TRUE and FALSE value as well which value of column AH matching with AI is required.
Example of wanted result
OXFORD	BLACKWELL PUBL LTD	TRUE	BLACKWELL
MOSBY	ISRAEL MEDICAL ASSOC JOURNAL	TRUE	FALSE
CELL	PERGAMON-ELSEVIER SCIENCE LTD 	TRUE	FALSE
AMER	PERGAMON-ELSEVIER SCIENCE LTD	TRUE	FALSE
BLACKWELL	MOSBY, INC	TRUE	MOBSY
	OXFORD UNIV PRESS	TRUE	OXFORD
	CELL PRESS	TRUE	CELL
	AMER COLL PHYSICIANS	TRUE	AMER
			
I want to be find the values of AK using formula/function.
答案1
得分: 0
=map(B4:B,lambda(Σ,iferror(+tocol(index(if(search(A4:A,Σ),A4:A)),3))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。



评论