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

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

Is there any formula to find cell reference from search function in Google sheets?

问题

  1. OXFORD BLACKWELL PUBL LTD TRUE BLACKWELL
  2. MOSBY ISRAEL MEDICAL ASSOC JOURNAL TRUE FALSE
  3. CELL PERGAMON-ELSEVIER SCIENCE LTD TRUE FALSE
  4. AMER PERGAMON-ELSEVIER SCIENCE LTD TRUE FALSE
  5. BLACKWELL MOSBY, INC TRUE MOBSY
  6. OXFORD UNIV PRESS TRUE OXFORD
  7. CELL PRESS TRUE CELL
  8. AMER COLL PHYSICIANS TRUE AMER
英文:

I have a range in column AH:AH19,

  1. OXFORD
  2. MOSBY
  3. CELL
  4. AMER
  5. 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

  1. BLACKWELL PUBL LTD
  2. ISRAEL MEDICAL ASSOC JOURNAL
  3. PERGAMON-ELSEVIER SCIENCE LTD
  4. PERGAMON-ELSEVIER SCIENCE LTD
  5. MOSBY, INC
  6. OXFORD UNIV PRESS
  7. CELL PRESS
  8. 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

  1. =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

  1. OXFORD BLACKWELL PUBL LTD TRUE BLACKWELL
  2. MOSBY ISRAEL MEDICAL ASSOC JOURNAL TRUE FALSE
  3. CELL PERGAMON-ELSEVIER SCIENCE LTD TRUE FALSE
  4. AMER PERGAMON-ELSEVIER SCIENCE LTD TRUE FALSE
  5. BLACKWELL MOSBY, INC TRUE MOBSY
  6. OXFORD UNIV PRESS TRUE OXFORD
  7. CELL PRESS TRUE CELL
  8. 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:

  1. =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:

确定