Return full cell contents if character exists within range

[amp_82]

Hi, I'm looking for a way to return a full cells value if it contains a certain character.


EG: I've a dataset where email address could be in any row from A-F and in column G I want to consolidate the emails so they are in a consistent column to later do a vlookup against

[pre]

123@abc	SAMPLE	SAMPLE	SAMPLE	SAMPLE	SAMPLE	        RESULT Column: 123@abc
SAMPLE	SAMPLE	bob@bob	SAMPLE	SAMPLE	SAMPLE	        RESULT Column: bob@bob

[/pre]

 

[Luke M]

I think this will work:

=INDEX(A2:F2,SUMPRODUCT((ISNUMBER(FIND("@",A2:F2)))*(COLUMN(A2:F2))))

 

[amp_82]

You sir are a genius. Works perfectly thank you.

 

[amp_82]

Is it cheeky to ask if you can break it down so i understand what the formula is doing?

 

[Luke M]

Not at all.

The FIND function looks at each of the cells and if the character we're looking for (@) is found, it gives an integer (representing the position within cell). If it's not found, it gives an error. The ISERROR function is then able to take this combination of errors and a single number into an array of True/False.

Then, that array gets multiplied against the column numbers. (a=1, b=2). Since there's only 1 True value, the SUMPRODUCT does 1*1+0*2+0*3+0*4+0*5 = 1.

The INDEX function then takes that number (1) and knows to looks at the 1st cell within the range. Does that help?

 

[Haseeb A]

Hi amp_82,


You could also use HLOOKUP,


=HLOOKUP("*@*",A2:F2,1,0)


Regards,

Haseeb

 

[Luke M]

Doh!

Kudos to you Haseeb. I had completely forgotten that VLOOKUP and HLOOKUP can use wildcards. =)

 

[amp_82]

wow, have been using v/hlookups daily for years, never knew they could support wild-cards, is how I first tried to solve this and then gave up!


Hats off to both of you. Many thanks.