有没有公式可以从Google表格的搜索函数中找到单元格引用?

huangapple go评论68阅读模式
英文:

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

			

enter image description here

I want to be find the values of AK using formula/function.

enter image description here

答案1

得分: 0

=map(B4:B,lambda(Σ,iferror(+tocol(index(if(search(A4:A,Σ),A4:A)),3))))

有没有公式可以从Google表格的搜索函数中找到单元格引用?

英文:

You may try:

=map(B4:B,lambda(Σ,iferror(+tocol(index(if(search(A4:A,Σ),A4:A)),3))))

有没有公式可以从Google表格的搜索函数中找到单元格引用?

huangapple
  • 本文由 发表于 2023年6月19日 10:36:25
  • 转载请务必保留本文链接:https://go.coder-hub.com/76503304.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定