How to do return multiple lookups within 1 cell

[Dave87]

Hi all,

I have a problem with this and cant think of anyway to do this except manually and i have circa 5000 rows. (Apologies for not using XL2BB but work has blocked its install, also this is an example file, real document has 5000 or so rows in sheet 1 and about 3500 in sheet2)

Sheet 1
This contains a range (can convert to table if required) which has multiple Ref's within individual cells, i'd like to bring the title and date from sheet2 to to the corresponding cells in columns B & C. The average number of "Refs" in a cell is generally less than 10 (however sometimes 0) and I have 3 cells with 14, 39, and 84 Refs

In Sheet 1 I've manually populated cells B2:C3, how can I formula/Macro this?

Sheet 1 (there is a space between the last number and the next Ref i.e. in cell A3 the text is REF-00008(space) ALT+Enter REF-00007. There is no space after the 7.

Ref	Title	Date
REF-00001	Anna Karenina by Leo Tolstoy. ..	2/03/1988
REF-00008 REF-00007	In Search of Lost Time by Marcel Proust The Great Gatsby by F. ..	2/04/1940 23/03/1991
REF-00002 REF-00006 REF-00008
REF-00005
REF-00006 REF-00007

SHEET2

REF	TITLE	DATE
REF-00001	Anna Karenina by Leo Tolstoy. ..	2-Mar-88
REF-00002	Madame Bovary by Gustav Flaubert. ..	5-Sep-69
REF-00003	War and Peace by Leo Tolstoy. ..	13-Jul-88
REF-00004	Lolita by Vladimir Nabokov. ..	6-Sep-59
REF-00005	The Adventures of Huckleberry Finn by Mark Twain. ..	7-Nov-68
REF-00006	Hamlet by William Shakespeare. ..	5-Oct-99
REF-00007	The Great Gatsby by F. ..	23-Mar-91
REF-00008	In Search of Lost Time by Marcel Proust	2-Apr-40

 

[Jdog12]

I used Vlookup to lookup title and date for Sheet 1.

I named Sheet 2 as Table2.

This Vlookup only works for rows with one entry, but it won't work for a merged field.

Maybe someone can write a Macro to resolve this.

Ref	Title	Date
REF-00001	Anna karenina by Leo Tolstoy	3/2/1988​
REF-00005	The Adventures of Hickleberry Finn by mark Twain	11/7/1968​
REF-00002	Mdame bovary by Gustav Flaubert	9/5/1969​
REF-00004	Lolita by Vladimir nabokov	9/6/1959​
REF-00003	War and Peace by Leo Tolstoy	7/13/1988​
REF-00005 REF-00002	#N/A​	#N/A​
	=VLOOKUP($A2,Table2,2,0)	=VLOOKUP($A2,Table2,3,0)
VLOOKUP IN B2 & C2	=VLOOKUP($A2,Table2,2,0)	=VLOOKUP($A2,Table2,3,0)

 

[Peter_SSs]

Does your version of Excel 365 have the FILTER function? If so, try these, copied down

Dave87 2020-05-31 1.xlsm
	A	B	C
1	Ref	Title	Date
2	REF-00001	Anna Karenina by Leo Tolstoy. ..	2/03/1988
3	REF-00008 REF-00007	The Great Gatsby by F. .. In Search of Lost Time by Marcel Proust	23/03/1991 2/04/1940
4	REF-00002 REF-00006 REF-00008	Madame Bovary by Gustav Flaubert. .. Hamlet by William Shakespeare. .. In Search of Lost Time by Marcel Proust	5/09/1969 5/10/1999 2/04/1940
5	REF-00005	The Adventures of Huckleberry Finn by Mark Twain. ..	7/11/1968
6	REF-00006 REF-00007	Hamlet by William Shakespeare. .. The Great Gatsby by F. ..	5/10/1999 23/03/1991
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=TEXTJOIN(CHAR(10),1,FILTER(Sheet2!B$2:B$9,ISNUMBER(FIND(Sheet2!$A$2:$A$9,$A2))))
C2:C6	C2	=TEXTJOIN(CHAR(10),1,TEXT(FILTER(Sheet2!C$2:C$9,ISNUMBER(FIND(Sheet2!$A$2:$A$9,$A2))),"d/mm/yyyy"))

Dave87 2020-05-31 1.xlsm
	A	B	C
1	REF	TITLE	DATE
2	REF-00001	Anna Karenina by Leo Tolstoy. ..	2-Mar-88
3	REF-00002	Madame Bovary by Gustav Flaubert. ..	5-Sep-69
4	REF-00003	War and Peace by Leo Tolstoy. ..	13-Jul-88
5	REF-00004	Lolita by Vladimir Nabokov. ..	6-Sep-59
6	REF-00005	The Adventures of Huckleberry Finn by Mark Twain. ..	7-Nov-68
7	REF-00006	Hamlet by William Shakespeare. ..	5-Oct-99
8	REF-00007	The Great Gatsby by F. ..	23-Mar-91
9	REF-00008	In Search of Lost Time by Marcel Proust	2-Apr-40
Sheet2

 

[Dave87]

Peter, you sir, are a genius! Thank you very much for your help

 

[Peter_SSs]

You are very welcome. Thanks for your kind words.