Find data in 2 possible Columns, return other data

[JTL9161]

Hope I can explain this clearly

I have 2 columns of data. I need to search for specific data "ABC" for example. When it finds it I need the data in the next column, meaning "GHI", but "ABC" could be in either column. It may not always be in the first column so if "ABC" was in the 2nd column I need the read the data in the 1st, so if "ABC" is in the 2nd column I would need to copy the "HIJ" data. If this is even doable.

ABC	GHI
DEF	QRS
XYZ	VWX
WXZ	PQR
HIJ	ABC
FGH	HUT

Thanks,
James

 

[Joe4]

Will it only ever appear exactly ONCE in these two columns?
If it may appear more than once, which record should it return the value for?

 

[JTL9161]

"ABC" would only show once in either column, as with all others. Each string entered only 1 time.

 

[Joe4]

OK, let's say that your data is in the range A1:B8, and we want to look up the value we place in cell E1.
Then place this formula in cell F1:

=IFERROR(INDEX(B1:B8,MATCH(E1,A1:A8,0)),INDEX(A1:A8,MATCH(E1,B1:B8,0)))

And here are two examples showing this working:

 

[JTL9161]

Perfect, I will give it a try, thanks

 

[Cubist]

Another option:

Book1
	A	B	C	D	E	F
1	ABC	GHI		ABC	ABC	GHI
2	DEF	QRS
3	XYZ	VWX
4
5
6	WXZ	PQR		ABC	HIJ	ABC
7	HIJ	ABC
8	FGH	HUT
Sheet7

Cell Formulas
Range	Formula
E1:F1,E6:F6	E1	=FILTER(A1:B3,(A1:A3=D1)+(B1:B3=D1))
Dynamic array formulas.