Searching for partial text using search function with lookup array?

[Dwexdwex]

I have to 2 spreadsheets

1st spreadsheet
----------------

Column(Name)
------
Alice
Ryan
Charles
Peter
David

2nd spreadsheet
----------------

Col(Name) Col(ID)
----- ------
McAlice 123
Ryan Lee 456
Charles Rock 789
Peterson 012
Davids 345

How do I set up a Search function with a lookup array?

A1 = alice

A1:A5 = McAlice,Ryan Lee, Charles Rock, Peterson, Davids

=Search(A1,A1:A5) <-- how do I create something like that to match 1st spread's values and tag the ID on them?

[Pete_UK]

Try this in B1 of Sheet1:

Please Login or Register  to view this content.

Hope this helps.

Pete

[Dwexdwex]

Thanks.

What if there are more than 1 match? How do I display all the matches, eg: Charles

Col(Name) Col(ID)
----- ------
McAlice 123
Ryan Lee 456
Charles Rock 789
Peterson 012
Davids 345
Charles Robert 333
De Franco Charles 111
Charles.jr 222

[windknife]

Try this,

B2

Please Login or Register  to view this content.

Committed with Ctrl+Shift+Enter.

See attached.
HTH,
WindKnife

[Dwexdwex]

Nice! Thanks.

seriously, how on earth did you come up with a long code like that?

care to explain the thought process to coming with this?

also for these, =IF(C1="","",C1+D1) & =IFERROR(INDEX(C:C,IF(ROW()>SUM($C$1:$C$10),"",LOOKUP(ROW(),$D$1:$D$10,ROW($D$1:$D$10)))),"")