Need to MATCH the first 4 chars in A2 against C:C then return adjacent cell in D:D (into B2)

[brashandcrass]

[FONT=&quot]Hello,[/FONT]
[FONT=&quot]Looking to[/FONT]

• MATCH the first 4 chars in cell A2
• against Col C:C
• If there's a match to the first 4 chars in A2 WITHIN the cell (could be the first, last, or even 12th word in the cell)...
• Return the adjacent cell's value (in D:D) into B2

[FONT=&quot]
Unfortunately the program to a conference I am attending is not very well laid out. I'll rip the contents from the PDF and place into Excel to be manipulated.

I have a list of speakers - almost 300 of them - in a "Who's Speaking at this Event" and want to match their last names against the Agenda where its description describes the session and the speakers participating. In an adjacent column will be the sessions time and location.[/FONT]
[FONT=&quot]For example, if John Abcdefghi is speaking in a session I want to attend, I want to scrub "Abcd" against a the Agenda column, then populate "Mon, 9:30a" from the adjacent column, to a cell next to his name in my own, personalized agenda.[/FONT]
[FONT=&quot]
Thanks![/FONT]

 

[Eric W]

Welcome to MrExcel.

Try:

=LOOKUP(2,1/SEARCH(LEFT(A2),C2:C100),D2:D100)

 

[brashandcrass]

Welcome to MrExcel.

Try:

=LOOKUP(2,1/SEARCH(LEFT(A2),C2:C100),D2:D100)

I had to make (A2)... (A2, 4) otherwise it worked! Could I ask you to explain what the 2 and the 1 behave in this formula (...2,1/SEARCH...), please?

Thanks!

 

[Eric W]

D'oh! Of course you need to add the ",4"! I don't know why I missed it, glad you caught it.

As far as the formula, it uses some poorly documented features of LOOKUP to make it work. First, it has some array processing functionality. Next, it ignores errors. And finally, if it can't find the value you're looking for, it returns the last number in the array.

So the SEARCH(LEFT(A2, 4),C2:C100) performs the SEARCH function on every cell in C2:C100 and returns the results in an array. The result of SEARCH (which is case-insensitive) is either the offset into the cell where the search string starts, or #VALUE !, so you get something like {#VALUE !,#VALUE !,#VALUE !,4,#VALUE !,#VALUE !,10,#VALUE !}. Putting the 1/ in front of the array calculates the reciprocal of each element, leaving the errors as errors, so it becomes: {#VALUE !,#VALUE !,#VALUE !,0.25,#VALUE !,#VALUE !,0.1,#VALUE !}. Since the results from SEARCH are all >=1, then the reciprocals will all be <=1. So when LOOKUP looks for 2, we KNOW it will never find it. So LOOKUP returns the last numeric value from the array, 0.1 in this example, then gives you the value from the D2:D100 range that's in the same position.

Hope this helps!

 

[brashandcrass]

The more I read it the more it makes sense but still trying to understand how/why looking for the "last numeric value from the array, 0.1" works.

Thanks for the explanation either way.