VLOOKUP For Multiple Matches

[srj1359]

Hello!

I have two sheets (Titles and Accounts).

The Accounts sheet has two columns: Participant Name (column A) and PRODUCT_ID (column B).

The Titles sheet has 32 columns, but the only one that matters is PRODUCT_ID (column AF).

In the Accounts sheet, multiple participants can have the same product ID.

In the Titles sheet, multiple titles can have the same product ID.

I'm trying to find a way to search to pull in the multiple participant names that match each product ID and the only way I think of is a VLOOKUP (though I'm open to other suggestions).

Please let me know if I need to clarify anything.

If it helps, I work in publishing, so think of it in terms of customer A bought a Science bundle and a History bundle. Customer B only bought a Science bundle. Each bundle has multiple books in it. How can I find out how many titles customer A has and how many customer B has?

Thank you in advance. I have been searching and trying different ideas for hours and would so appreciate some help from someone with more knowledge than me

 

[alansidman]

With Power Query, you can join the two tables/ranges for common fields and extract the pertinent data. If you provide some samples of both sheets 8-12 records only then I can show you how to do this. Otherwise you will need to employ some VBA .

 

[srj1359]

Hi @alansidman

Thank you for your response! I've been spending most of my time these past few days trying to get a power query to work, so if you can help, that would be great.

I'm operating on Microsoft Excel 2016.

The main thing that has been causing me trouble here is this is a tool I'm creating for many people to use and this will be updated frequently so the file name will change, which means I can't incorporate into the VBA.

I've attached two screenshots of my data (I altered it to protect customers, so please let me know if I need to give more detail). I had created the "Export" tab as a place to put the power pivot, but as I said, have been having trouble.

Thank you, thank you, thank you for any help you can offer!!!

 

[Dave87]

I had similar issues before and Peter kindly provided a great solutions

How to do return multiple lookups within 1 cell

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...

www.mrexcel.com

Combining Excel Macro/Function & Excel 365 formulas

Hi everyone, This is me being kind of lazy and trying to do things in one step instead of jumping between excel365 and the Excel app, @Peter_SSs has previously helped me out with the following 2 problems Extract multiple specified values from cell and return in 1 cell...

www.mrexcel.com

Extract multiple specified values from cell and return in 1 cell

Hello everyone, I need some help on this, in column A i have a list of various fruit, within column C i have sentences which may contain one or more of the fruit listed in Column A. In column E i would like to list the fruit from column A which is contained in column C. Also i would like to...

www.mrexcel.com

Maybe one of them could be applied

 

[srj1359]

Thank you, @Dave87!

@Peter_SSs - I looked over the links Dave sent above and the first two are very close to what I need.

Above I told alansidman I'm operating on Microsoft Excel 2016, but I was mistaken. I'm actually using Microsoft 365, but it's a business license, so the system administrator installs the updates and I'm afraid it does not appear as though I have the FILTER function (just FILTERXML).

I did try it on my personal computer (which also has 365), and this is almost exactly right, but I wondered if there's a way to add a space in place of or in addition to wrapping text so that I am able to separate multiple results with text to columns?

Also, if I incorporate this formula into a macro, will others accessing this document that don't have 365 get an error?

Lastly, since it seems I don't have the most recent 365 update on my work computer, I also tried the MultipleLookups UDF, but I keep getting a #VALUE! error and am not sure what I'm doing wrong unless it was specific just to Dave's request?

Any help you can provide is greatly appreciated. Thank you!!

 

[Peter_SSs]

If you are going to use a macro, then you probably don't need a formula at all as the macro could do it all.
If you do use a formula with the FILTER function then all users will need to have that function.
You have shown us some sample data but ..
- we cannot copy from a picture to test, and
- we cannot see your expected results

Therefore, can you give us the sample data again but also include the expected results (you mentioned text to columns so make sure the expected results are in the final form that you want).
When doing the above, if you can use XL2BB then we can easily copy for testing.

 

[srj1359]

@Peter_SSs thank you for the XL2BB article. I was wondering how people posted those captures. I hope the below is helpful, but please let me know if more is needed from me.

Book1.xlsx
	A	B
1	Participant Name	PRODUCT_ID
2	ABC University	AAA-00001
3	ABC University	AAA-00002
4	ABC University	AAA-00003
5	ABC University	AAA-00004
6	ABC University	AAA-00005
7	DEF University	AAA-00001
8	DEF University	AAA-00002
9	DEF University	AAA-00003
10	DEF University	AAA-00004
11	DEF University	AAA-00005
12	DEF University	ZZZ-00001
13	DEF University	ZZZ-00002
14	DEF University	ZZZ-00003
15	DEF University	ZZZ-00004
16	DEF University	ZZZ-00005
Accounts

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1	publication_title	print_identifier	online_identifier	date_first_issue_online	num_first_vol_online	num_first_issue_online	date_last_issue_online	num_last_vol_online	num_last_issue_online	title_url	first_author	title_id	embargo_info	coverage_depth	notes	publisher_name	publication_type	date_monograph_published_print	date_monograph_published_online	monograph_volume	monograph_edition	first_editor	parent_publication_title_id	preceding_publication_title_id	access_type	OCN	DOI	MARC Control Number	Subject	Module	PLATFORM	PRODUCT_ID
2	In Search of Lost Time by Marcel Proust																															AAA-00001
3	Ulysses by James Joyce																															AAA-00002
4	Don Quixote by Miguel de Cervantes																															AAA-00003
5	The Great Gatsby by F. Scott Fitzgerald																															AAA-00004
6	One Hundred Years of Solitude by Gabriel Garcia Marquez																															AAA-00005
7	Moby **** by Herman Melville																															ZZZ-00001
8	War and Peace by Leo Tolstoy																															ZZZ-00002
9	Lolita by Vladimir Nabokov																															ZZZ-00003
10	Hamlet by William Shakespeare																															ZZZ-00004
11	The Catcher in the Rye by J. D. Salinger																															ZZZ-00005
12	The Odyssey by Homer																															AAA-00001
13	The Brothers Karamazov by Fyodor Dostoyevsky																															AAA-00002
14	Crime and Punishment by Fyodor Dostoyevsky																															AAA-00003
15	Madame Bovary by Gustave Flaubert																															AAA-00004
16	The Divine Comedy by Dante Alighieri																															AAA-00005
17	The Adventures of Huckleberry Finn by Mark Twain																															ZZZ-00001
18	Alice's Adventures in Wonderland by Lewis Carroll																															ZZZ-00002
19	Pride and Prejudice by Jane Austen																															ZZZ-00003
20	Wuthering Heights by Emily Brontë																															ZZZ-00004
21	To the Lighthouse by Virginia Woolf																															ZZZ-00005
Titles

 

[Peter_SSs]

Thanks for getting XL2BB going.
BTW, you can hide irrelevant columns to keep the screen shot smaller (eg columns B:AE in 'Titles')

Now, what about this?

Therefore, can you give us the sample data again but also include the expected results (you mentioned text to columns so make sure the expected results are in the final form that you want).

 

[srj1359]

@Peter_SSs

As you can see, I have two sheets (Titles and Accounts). The Accounts sheet has two columns: Participant Name (column A) and PRODUCT_ID (column B). The Titles sheet has 32 columns, but the only one that matters is PRODUCT_ID (column AF).

In the Accounts sheet, multiple participants can have the same product ID. In the Titles sheet, multiple titles can have the same product ID. I'm trying to find a way to search to pull in the multiple participant names that match each product ID (into column AG on the Titles sheet, for example)

Second sheet is captured again below with those rows hidden to make it easier to see:

Book1.xlsx
	A																															AF
1	publication_title																															PRODUCT_ID
2	In Search of Lost Time by Marcel Proust																															AAA-00001
3	Ulysses by James Joyce																															AAA-00002
4	Don Quixote by Miguel de Cervantes																															AAA-00003
5	The Great Gatsby by F. Scott Fitzgerald																															AAA-00004
6	One Hundred Years of Solitude by Gabriel Garcia Marquez																															AAA-00005
7	Moby **** by Herman Melville																															ZZZ-00001
8	War and Peace by Leo Tolstoy																															ZZZ-00002
9	Lolita by Vladimir Nabokov																															ZZZ-00003
10	Hamlet by William Shakespeare																															ZZZ-00004
11	The Catcher in the Rye by J. D. Salinger																															ZZZ-00005
12	The Odyssey by Homer																															AAA-00001
13	The Brothers Karamazov by Fyodor Dostoyevsky																															AAA-00002
14	Crime and Punishment by Fyodor Dostoyevsky																															AAA-00003
15	Madame Bovary by Gustave Flaubert																															AAA-00004
16	The Divine Comedy by Dante Alighieri																															AAA-00005
17	The Adventures of Huckleberry Finn by Mark Twain																															ZZZ-00001
18	Alice's Adventures in Wonderland by Lewis Carroll																															ZZZ-00002
19	Pride and Prejudice by Jane Austen																															ZZZ-00003
20	Wuthering Heights by Emily Brontë																															ZZZ-00004
21	To the Lighthouse by Virginia Woolf																															ZZZ-00005
Titles

 

[Peter_SSs]

I'm trying to find a way to search to pull in the multiple participant names that match each product ID (into column AG on the Titles sheet, for example)

So you no longer want the values in separate columns as suggested by your mention of Text to Columns in post 5?