Find in which columns lies the text?-CodePudding

Thanks for reading this!

Lets say we have 2 columns in this workbook called "Data":

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

In another workbook, I want the user to input some random name in a cell.

Below that cell, I want to be able to show him/her, in which column that name lies. E.g. if he types "Joshua", I want to be shown below:

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

I prefer using a formula, instead of VBA, as it would mess with my not-so-experienced end-user!

Notes: See below my attempt if you find it useful:

(1) I tried that using a nested IF FILTER functions inside, but the IF returns only the first TRUE column, like this:

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

Here is my actual formula, where I'm referring to split ranges in sheet "6", where I have 4 columns:

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,""))))

CodePudding user response：

You could use:

Formula in D2:

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

CodePudding user response：

You can get the column numbers with this formula (original data on worksheet "10")

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

Although I suggest reducing the range references from full columns to something shorter to reduce calculation times.

With Office 365, you can convert the column number to the letter with this:

=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 " & LEFT(adr,FIND("$",adr)-1))