chimp_spanner
New Member
- Joined
- May 6, 2020
- Messages
- 6
- Office Version
- 365
- Platform
- MacOS
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!!
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!!