Finding and returning one of multiple potential values from a range

[Richard Hill]

Hi Everyone

Unfortunately I cannot download XL2BB (work IT security), so I have had to use the below screenshot.

I am working on a large formula and have got stuck on one component. For ease, I have broken out this component and simplified it as follows:

I have 5 values: MYTPP; MAPTM; EGPSD; ESALR; OMSLV.
I know that in each row of data, one of those values will appear somewhere in columns B:F. The exact column will vary from row to row.
I would like a formula in column G that will look for these 5 values in columns B:F and return whichever it finds.

In the example below, I have manually typed the expected result.

I have tried several array based formulas but cannot get one to work. I could use some convoluted nested ISNA/IF statement, but am looking for something shorter and simpler, because this spreadsheet will be handed over to others to use and maintain with less experience than me.

Many thanks in advance.

 

[Eric W]

Maybe:

Book1
	A	B	C	D	E	F	G
1		1st Dicharge Port	2nd Dicharge Port	3rd Dicharge Port	4th Dicharge Port	5th Dicharge Port	HUB PORT
2		LKCMB	MYTPP	NLROT	IEDUB		MYTPP
3		LKCMB	MYTPP	NLROT			MYTPP
4		MYTPP	GBFXS				MYTPP
5		KRULS	CNNPO	MAPTM	SLFNA		MAPTM
6		CNFOO	HKHKG	EGPSD	NLROT	SEGOT	EGPSD
7		CNFOO	HKHKG	SGSIN	ESALR	RULED	ESALR
Sheet39

Cell Formulas
Range	Formula
G2:G7	G2	=LOOKUP(2,1/COUNTIF(B2:F2,{"MYTPP","MAPTM","EGPSD","ESALR","OMSLV"}),{"MYTPP","MAPTM","EGPSD","ESALR","OMSLV"})

 

[Eric W]

Slightly shorter formula where you only have to type in the values once:

=INDEX(B2:F2,AGGREGATE(15,6,MATCH({"MYTPP","MAPTM","EGPSD","ESALR","OMSLV"},B2:F2,0),1))

 

[Richard Hill]

Thank you very much for your assistance.
I think I will use the aggregate function due to the 'single typing' as this will sit within a erlong formula with the details repeated multiple times.

 

[Eric W]

Happy to help.