VLOOKUP - how to search for only part of the Lookup Value

[chimp_spanner]

Hey everyone! Hoping someone can help me here. I've asked over at Reddit but still can't quite get to where I wanna be.

The attached image should show what I'm trying to do. Obviously in practice the array would be on a separate worksheet. In the Mix Type bit I've manually typed in what I'd like the formula to return based on the lookup. So with the array defined as a range called Mix_Type, the formula is;

=vlookup(A2,Mix_Type,2,FALSE)

When I do this I get #N/A. If I set to TRUE, every file returns "Radio Edit".

I think I know what the problem is in as much as it's showing #N/A because there's nothing in the first column of the array with that exact name (every song on an album has a different name). How can I get it to match a file name to a mix type if it contains a relevant word anywhere in the lookup value?

It's complicated in that the file names are always different lengths. And there are also multiple variants on each file according to length, so for example;

ALIBI-Happy Days_Full w Lyrical Male Vox
ALIBI-Happy Days_Full w Lyrical Male Vox_5
ALIBI-Happy Days_Full w Lyrical Male Vox_15
ALIBI-Happy Days_Full w Lyrical Male Vox_30

I'm not really great at Excel so if possible, please explain it to me like I'm five haha. Thanks so much in advance for any assistance!!

 

[Toadstool]

Hi Chimp_Spanner,

A wildcard ("*") will let you do a search on part of a string but you have the added complication of some terms appearing twice so a simple search will just return the first hit.

Book2
	A	B
1	Track Title	Mix Type
2	Boom-Boom Lyrical Female Vox	Vocal Mix
3	Tea for Two	Radio Edit
4	Bones_Stem Vox	Stem
5	Boom-Boom Lyrical Female	Radio Edit
6
7
8
9	Find in Track Title	Show in Mix Type
10	Lyrical Male	#N/A
11	Lyrical Female	Vocal Mix
12	Vox	Vocal Mix
13	_STEM	Stem
Sheet1

Cell Formulas
Range	Formula
B10:B13	B10	=INDEX($B$2:$B$6,MATCH("*"&A10&"*",$A$2:$A$6,0))

If you could copy&paste your cells it would make it easier for people rather than posting an image.

 

[chimp_spanner]

Hi Chimp_Spanner,

A wildcard ("*") will let you do a search on part of a string but you have the added complication of some terms appearing twice so a simple search will just return the first hit.

Book2
	A	B
1	Track Title	Mix Type
2	Boom-Boom Lyrical Female Vox	Vocal Mix
3	Tea for Two	Radio Edit
4	Bones_Stem Vox	Stem
5	Boom-Boom Lyrical Female	Radio Edit
6
7
8
9	Find in Track Title	Show in Mix Type
10	Lyrical Male	#N/A
11	Lyrical Female	Vocal Mix
12	Vox	Vocal Mix
13	_STEM	Stem
Sheet1

Cell Formulas
Range	Formula
B10:B13	B10	=INDEX($B$2:$B$6,MATCH("*"&A10&"*",$A$2:$A$6,0))

If you could copy&paste your cells it would make it easier for people rather than posting an image.

Hey Toadstool! Thanks so much for the reply, and apologies for posting an image instead of cells (new here!). I'll paste below if it helps;

Track Title	Mix Type	Find It	Show It
ALIBI-Summer Vibes_Full w Lyrical Male Vox		Lyrical	Vocal Mix
ALIBI-Summer Vibes_Instrumental Mix		Vox	Instrumental Mix
ALIBI-Summer Vibes_Rhythm Mix		_Instrumental	Instrumental Mix
ALIBI-Summer Vibes_Sparse Mix		_Rhythm Mix	Rhythm Mix
ALIBI-Summer Vibes_Sparse Rhythm Mix		_Sparse Mix	Sparse Mix
ALIBI-Summer Vibes_Drums and Bass Mix		_Sparse Rhythm Mix	Sparse Rhythm Mix
ALIBI-Summer Vibes_Drums and FX Mix		_Drums and	Drums and Bass Mix
ALIBI-Summer Vibes_Holiday Mix		_Holiday	Holiday Mix
ALIBI-Summer Vibes_Radio Edit		_Radio	Radio Edit
ALIBI-Summer Vibes_STEM Guitars		_STEM	Stem
ALIBI-Summer Vibes_STEM Vox
ALIBI-Summer Vibes_STEM FX
ALIBI-Forgotten Dreams_Full w Vox Samples
ALIBI-Forgotten Dreams_Instrumental Mix
ALIBI-Forgotten Dreams_Rhythm Mix
ALIBI-Forgotten Dreams_Sparse Mix
ALIBI-Forgotten Dreams_Sparse Rhythm Mix
ALIBI-Forgotten Dreams_Drums and Bass Mix
ALIBI-Forgotten Dreams_Holiday Mix
ALIBI-Forgotten Dreams_Radio Edit
ALIBI-Forgotten Dreams_STEM Drums
ALIBI-Forgotten Dreams_STEM Vox Samples
ALIBI-Forgotten Dreams_STEM Violins

Didn't quite follow the example you gave, although interesting that you're using INDEX and MATCH as I hadn't looked into those. Ultimately the goal is that I can have a formula that I can just Fill Down from B2:Bx.

 

[chimp_spanner]

Also i realise that I might run into trouble having Full w Vox being instrumental, while Full w Lyrical Vox being vocal. But I'm hoping if it does it by what it encounters first, I should be okay. As the file names are always presented in this order (I do this first in another program).

 

[jasonb75]

See if this helps. Note that I've swapped the entries for 'Vox' and 'Lyrical' in the 'Find it' and 'Show it' columns. Where 2 (or more) possible matches could be found, the match with the higher priority needs to be lower in the list.

The List.xlsx
	A	B	C	D
1	Track Title	Mix Type	Find It	Show It
2	ALIBI-Summer Vibes_Full w Lyrical Male Vox	Vocal Mix	Vox	Instrumental Mix
3	ALIBI-Summer Vibes_Instrumental Mix	Instrumental Mix	Lyrical	Vocal Mix
4	ALIBI-Summer Vibes_Rhythm Mix	Rhythm Mix	_Instrumental	Instrumental Mix
5	ALIBI-Summer Vibes_Sparse Mix	Sparse Mix	_Rhythm Mix	Rhythm Mix
6	ALIBI-Summer Vibes_Sparse Rhythm Mix	Sparse Rhythm Mix	_Sparse Mix	Sparse Mix
7	ALIBI-Summer Vibes_Drums and Bass Mix	Drums and Bass Mix	_Sparse Rhythm Mix	Sparse Rhythm Mix
8	ALIBI-Summer Vibes_Drums and FX Mix	Drums and Bass Mix	_Drums and	Drums and Bass Mix
9	ALIBI-Summer Vibes_Holiday Mix	Holiday Mix	_Holiday	Holiday Mix
10	ALIBI-Summer Vibes_Radio Edit	Radio Edit	_Radio	Radio Edit
11	ALIBI-Summer Vibes_STEM Guitars	Stem	_STEM	Stem
12	ALIBI-Summer Vibes_STEM Vox	Stem
13	ALIBI-Summer Vibes_STEM FX	Stem
14	ALIBI-Forgotten Dreams_Full w Vox Samples	Instrumental Mix
15	ALIBI-Forgotten Dreams_Instrumental Mix	Instrumental Mix
16	ALIBI-Forgotten Dreams_Rhythm Mix	Rhythm Mix
17	ALIBI-Forgotten Dreams_Sparse Mix	Sparse Mix
18	ALIBI-Forgotten Dreams_Sparse Rhythm Mix	Sparse Rhythm Mix
19	ALIBI-Forgotten Dreams_Drums and Bass Mix	Drums and Bass Mix
20	ALIBI-Forgotten Dreams_Holiday Mix	Holiday Mix
21	ALIBI-Forgotten Dreams_Radio Edit	Radio Edit
22	ALIBI-Forgotten Dreams_STEM Drums	Stem
23	ALIBI-Forgotten Dreams_STEM Vox Samples	Stem
24	ALIBI-Forgotten Dreams_STEM Violins	Stem
Sheet15

Cell Formulas
Range	Formula
B2:B24	B2	=LOOKUP(2,1/ISNUMBER(SEARCH($C$2:$C$11,A2)),$D$2:$D$11)

 

[chimp_spanner]

See if this helps. Note that I've swapped the entries for 'Vox' and 'Lyrical' in the 'Find it' and 'Show it' columns. Where 2 (or more) possible matches could be found, the match with the higher priority needs to be lower in the list.

The List.xlsx
	A	B	C	D
1	Track Title	Mix Type	Find It	Show It
2	ALIBI-Summer Vibes_Full w Lyrical Male Vox	Vocal Mix	Vox	Instrumental Mix
3	ALIBI-Summer Vibes_Instrumental Mix	Instrumental Mix	Lyrical	Vocal Mix
4	ALIBI-Summer Vibes_Rhythm Mix	Rhythm Mix	_Instrumental	Instrumental Mix
5	ALIBI-Summer Vibes_Sparse Mix	Sparse Mix	_Rhythm Mix	Rhythm Mix
6	ALIBI-Summer Vibes_Sparse Rhythm Mix	Sparse Rhythm Mix	_Sparse Mix	Sparse Mix
7	ALIBI-Summer Vibes_Drums and Bass Mix	Drums and Bass Mix	_Sparse Rhythm Mix	Sparse Rhythm Mix
8	ALIBI-Summer Vibes_Drums and FX Mix	Drums and Bass Mix	_Drums and	Drums and Bass Mix
9	ALIBI-Summer Vibes_Holiday Mix	Holiday Mix	_Holiday	Holiday Mix
10	ALIBI-Summer Vibes_Radio Edit	Radio Edit	_Radio	Radio Edit
11	ALIBI-Summer Vibes_STEM Guitars	Stem	_STEM	Stem
12	ALIBI-Summer Vibes_STEM Vox	Stem
13	ALIBI-Summer Vibes_STEM FX	Stem
14	ALIBI-Forgotten Dreams_Full w Vox Samples	Instrumental Mix
15	ALIBI-Forgotten Dreams_Instrumental Mix	Instrumental Mix
16	ALIBI-Forgotten Dreams_Rhythm Mix	Rhythm Mix
17	ALIBI-Forgotten Dreams_Sparse Mix	Sparse Mix
18	ALIBI-Forgotten Dreams_Sparse Rhythm Mix	Sparse Rhythm Mix
19	ALIBI-Forgotten Dreams_Drums and Bass Mix	Drums and Bass Mix
20	ALIBI-Forgotten Dreams_Holiday Mix	Holiday Mix
21	ALIBI-Forgotten Dreams_Radio Edit	Radio Edit
22	ALIBI-Forgotten Dreams_STEM Drums	Stem
23	ALIBI-Forgotten Dreams_STEM Vox Samples	Stem
24	ALIBI-Forgotten Dreams_STEM Violins	Stem
Sheet15

Cell Formulas
Range	Formula
B2:B24	B2	=LOOKUP(2,1/ISNUMBER(SEARCH($C$2:$C$11,A2)),$D$2:$D$11)

^ Mate. I have been banging my head against the desk ALL day and that appears to have done it perfectly. Gonna test it out in a few different scenarios but it looks like you nailed it. Thank you!!!!

 

[chimp_spanner]

See if this helps. Note that I've swapped the entries for 'Vox' and 'Lyrical' in the 'Find it' and 'Show it' columns. Where 2 (or more) possible matches could be found, the match with the higher priority needs to be lower in the list.

Just a couple of quick follow up questions. I got it working when everything's on one sheet. I'm trying to do it with the search/return table on another sheet and it's giving me a #N/A. Do I have to change something in that initial part (2,1/ISNUMBER) and what are they actually doing? Thanks!

 

[chimp_spanner]

Nevermind, I'm an idiot. I tried putting the formula into my main data sheet and lookup value was blank because I hadn't imported any data yet. It's been a long day :D Apologies for the multi posts - I can't figure out how to edit/delete posts.

 

[jasonb75]

To (try to) explain how it works,
SEARCH(list, title) creates an array of numbers (list item found in title) or errors (item not found).
ISNUMBER converts numbers to TRUE and errors to FALSE.
LOOKUP(2,1/ finds the postition of the last TRUE match i the list by converting the FALSE results back to errors.
It would work here without ISNUMBER, I just added it to the formula out of habit. There are times when it might be needed but here it is not necessary.

As for editing posts, there is an 'edit' link in the bottom left corner of each post (next to report) but you only get a 10 minute window to edit and save from when you first submit the post.
Deleting posts is not possible, that can only be done by admin, usually only posts that break the rules get edited or deleted, (hopefully I'm not breaking rule 9 here) ?