Lookup a string within array and return the first value of the column

I have been searching for the answer to what I hope is a simple formula that I just can not figure out.

I need a formula that will search for a string within an entire tab/sheet and return the name of the column in which it was found.

For example I have columns A-G all titled by Director, and the cells below are the branches that are assigned to that director.

The goal is to be able to type a branch number into a search sheet and the Directors name be returned.

Kind Regards

Upvotes: 1

Answers (3)

Reputation: 1

try:

=INDEX(QUERY(FLATTEN(IF(REGEXMATCH(LOWER(E2), 
 IF(A2:C="", "×", LOWER(A2:C))), A1:C1, )), 
 "where Col1 is not null", 0))

Upvotes: 0

Reputation: 7773

Assuming the directors are in row 1 on a tab called Sheet1. and the branches are all starting in row 2.

On a new blank sheet, with a Branch name in cell A2, put this formula in cell B2.

=ARRAYFORMULA(VLOOKUP(A2,SPLIT(FLATTEN(Sheet1!A2:G&"|"&Sheet1!A1:G1),"|",0,0),2,0))

You can read about:

SPLIT() FLATTEN(), and VLOOKUP()

Upvotes: 2

Reputation: 11968

You can use FILTER:

=FILTER(A1:G1,COLUMN(A1:G1)=SUM((I2=A2:G5)*COLUMN(A2:G5)))

Upvotes: 0