Index Match across multiple columns

[excelscope]

I am attempting to look up a certain email from multiple columns and return the branch number. This is just a snippet there are a TON of branches with employee emails.
Ex: If I look through columns B, C, and D, for an emaily@gmail.com and then have the function return the branch number.

I was attempting to use an index, match formula but the match function isn't working across multiple columns.

 

[awoohaw]

Try:
(This works if the emails are all unique, if they are not, please clarify your rules)
This is for Excel 2016 and later, incl 365. If you have earlier then you need to change enter with CNTR-SHFT-ENTR keystrokes, or use SUMPRODUCT function instead of SUM. You should update your profile to show what version of excel you use.

Mr Excel Questions 71.xlsm
	A	B	C	D	E	F
1
2		Branch	email	email	email	email
3		58	email1@gmail.com	emailA@gmail.com	emailF@gmail.com	emailK@gmail.com
4		104	email2@gmail.com	emailB@gmail.com	emailG@gmail.com	emailL@gmail.com
5		153	email3@gmail.com	emailC@gmail.com	emailH@gmail.com	emailM@gmail.com
6
7		target:	Branch
8		emailG@gmail.com	104	<<< 365
9			104	<<< SUMPRODUCT
10			104	<<< CNTL-SHFT-ENTR
excelscope

Cell Formulas
Range	Formula
C8	C8	=INDEX($B$3:$B$5,SUM((B8=$C$3:$F$5)*((ROW($B$3:$B$5)-2))))
C9	C9	=INDEX($B$3:$B$5,SUMPRODUCT((B8=$C$3:$F$5)*((ROW($B$3:$B$5)-2))))
C10	C10	=INDEX($B$3:$B$5,SUM((B8=$C$3:$F$5)*((ROW($B$3:$B$5)-2))))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[alansidman]

An alternative is with Power Query. Create a parameter with your lookup value
Import your table to PQ editor.
Unpivot your columns so that each email is associated in a singular column to the Branch
Import your lookup to the PQ editor
Filter your email list by linking the lookup to the field in question.
Close and load to Native Excel.
This is an effective means if you have lots of columns and rows.
Any updates to your original data is automatically included in your queries when you select Refresh All

 

[Momentman]

Try:
(This works if the emails are all unique, if they are not, please clarify your rules)
This is for Excel 2016 and later, incl 365. If you have earlier then you need to change enter with CNTR-SHFT-ENTR keystrokes, or use SUMPRODUCT function instead of SUM. You should update your profile to show what version of excel you use.

Mr Excel Questions 71.xlsm
	A	B	C	D	E	F
1
2		Branch	email	email	email	email
3		58	email1@gmail.com	emailA@gmail.com	emailF@gmail.com	emailK@gmail.com
4		104	email2@gmail.com	emailB@gmail.com	emailG@gmail.com	emailL@gmail.com
5		153	email3@gmail.com	emailC@gmail.com	emailH@gmail.com	emailM@gmail.com
6
7		target:	Branch
8		emailG@gmail.com	104	<<< 365
9			104	<<< SUMPRODUCT
10			104	<<< CNTL-SHFT-ENTR
excelscope

Cell Formulas
Range	Formula
C8	C8	=INDEX($B$3:$B$5,SUM((B8=$C$3:$F$5)*((ROW($B$3:$B$5)-2))))
C9	C9	=INDEX($B$3:$B$5,SUMPRODUCT((B8=$C$3:$F$5)*((ROW($B$3:$B$5)-2))))
C10	C10	=INDEX($B$3:$B$5,SUM((B8=$C$3:$F$5)*((ROW($B$3:$B$5)-2))))
Press CTRL+SHIFT+ENTER to enter array formulas.

Excellent options. My only add would be that if the data does shift downwards, the -2 would have to be adjusted based on the start row of the data

in 365, i may approach it this way

Book1
	A	B	C	D	E	F	G
1
2		Branch	email	email	email	email
3		58	email1@gmail.com	emailA@gmail.com	emailF@gmail.com	emailK@gmail.com
4		104	email2@gmail.com	emailB@gmail.com	emailG@gmail.com	emailL@gmail.com
5		153	email3@gmail.com	emailC@gmail.com	emailH@gmail.com	emailM@gmail.com
6
7		target:	Branch
8		emailG@gmail.com	104	<<< 365
Sheet5

Cell Formulas
Range	Formula
C8	C8	=INDEX($B$3:$B$5,SUM((B8=$C$3:$F$5)*(SEQUENCE(ROWS($C$3:$F$5)))))

 

[awoohaw]

Excellent options. My only add would be that if the data does shift downwards, the -2 would have to be adjusted based on the start row of the data

in 365, i may approach it this way

Book1
	A	B	C	D	E	F	G
1
2		Branch	email	email	email	email
3		58	email1@gmail.com	emailA@gmail.com	emailF@gmail.com	emailK@gmail.com
4		104	email2@gmail.com	emailB@gmail.com	emailG@gmail.com	emailL@gmail.com
5		153	email3@gmail.com	emailC@gmail.com	emailH@gmail.com	emailM@gmail.com
6
7		target:	Branch
8		emailG@gmail.com	104	<<< 365
Sheet5

Cell Formulas
Range	Formula
C8	C8	=INDEX($B$3:$B$5,SUM((B8=$C$3:$F$5)*(SEQUENCE(ROWS($C$3:$F$5)))))

Thanks @Momentman , even when using 365 I forget about SEQUENCE and revert to the older ways of indexing rows. If the user doesn't have SEQUENCE then that is the only way I know of.

 

[Peter_SSs]

@excelscope
Welcome to the MrExcel board!
Is this a Google Sheets question?