Найдите, в каких столбцах находится текст?

#excel #excel-formula #office365

#excel #excel-формула #office365

Вопрос:

Спасибо, что прочитали это!

Допустим, у нас есть 2 столбца в этой книге под названием «Данные»:

 | Column A | Column B |
| -------- | -------------- |
| Joshua    | Noah          |
| Daniel   | Joshua         |

В другой книге я хочу, чтобы пользователь вводил какое-то случайное имя в ячейку.

Под этой ячейкой я хочу иметь возможность показать ему / ей, в каком столбце находится это имя. Например, если он наберет «Джошуа», я хочу, чтобы меня показали ниже:

 ||
|--|
|Column A|
|Column B|

Я предпочитаю использовать формулу вместо VBA, так как это может помешать моему не очень опытному конечному пользователю!

Примечания: Смотрите Ниже мою попытку, если вы найдете ее полезной:

(1) Я попробовал это, используя вложенные функции IF FILTER внутри, но IF возвращает только первый истинный столбец, например:

 | |
|------|
| Column A |
| Column A |

Вот моя фактическая формула, в которой я имею в виду диапазоны разделения на листе «6», где у меня 4 столбца:

 IF(NOT(ISERROR(FILTER('6'!B4#,ISNUMBER(SEARCH($F$4,'6'!B4#))))),'6'!$B$1,
IF(NOT(ISERROR(FILTER('6'!D4#,ISNUMBER(SEARCH($F$4,'6'!D4#))))),'6'!$D$1,
IF(NOT(ISERROR(FILTER('6'!F4#,ISNUMBER(SEARCH($F$4,'6'!F4#))))),'6'!$F$1,
IF(NOT(ISERROR(FILTER('6'!H4#,ISNUMBER(SEARCH($F$4,'6'!H4#))))),'6'!$H$1,""))))

1. Если вы согласны с пробелами, вам может понадобиться VLOOKUP или HLOOKUP. Взгляните на эти две функции и подумайте об использовании по одной для каждого столбца имен в полях под вашим полем «Ввод». Но вы также должны учитывать, что произойдет, если столбец имеет одно и то же имя несколько раз (или это возможно) MSFT DOCS — VLOOKUP

Ответ №1:

Вы могли бы использовать:

Формула в D2 :

 =FILTER(TRANSPOSE(A1:B1),MMULT(--(TRANSPOSE(A2:B3)=D1),SEQUENCE(ROWS(A2:B3),,,0)),"")

1. Мне нравится этот подход, это 2 функции, которые я никогда не использую сам. Но когда я пытаюсь использовать ваше решение для Daniel и Noah, я получаю неправильное имя столбца.

2. @W_O_L_F. Правильно, отличное место. Не слишком много думал об этом, но сначала мне нужно было перенести данные.

3. Это работает аккуратно! Большое спасибо!

Ответ №2:

Вы можете получить номера столбцов с помощью этой формулы (исходные данные на листе «10»)

 =AGGREGATE(15,6,1/('10'!A:D="Joshua")*COLUMN('10'!A:D),SEQUENCE(COUNTIF('10'!A:D,"Joshua")))

Хотя я предлагаю сократить ссылки на диапазон от полных столбцов до чего-то более короткого, чтобы сократить время вычислений.

В Office 365 вы можете преобразовать номер столбца в букву с помощью этого:

 =LET(col,AGGREGATE(15,6,1/('10'!A:D="Joshua")*COLUMN('10'!A:D),SEQUENCE(COUNTIF('10'!A:D,"Joshua"))),
          adr,ADDRESS(1,col,2),
         "Column " amp; LEFT(adr,FIND("$",adr)-1))

1. Спасибо! Ваша последняя формула соответствует моему вопросу почти на 100%. Я просто изменил формулу, чтобы показывать имена заголовков столбцов вместо букв столбцов. Итак, от этого: «Столбец» amp; LEFT(adr, FIND(«$», adr)-1)) к этому IF(LEFT(adr,FIND(«$», adr) -1)=»H», «Имя столбца H», IF(LEFT(adr,FIND(«$», adr)-1)=»J»,»Имя столбца J»,»»)))