英文:
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))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论