How would I use a Vlookup with multiple tildes in the text string? It would look something like this:
SWBC ~ 7797_Dodge_Challenger ~ Video ~ New
What would the VLookup formula be?
4
-
Hi and welcome to SU. Please provide more context. What have you tried so far?– FlexYourDataCommented Jan 18 at 18:50
-
Originally I tried this formula that I found on here but it didn't work because the text doesn't start with ~ =VLOOKUP(SUBSTITUTE(C1,"~","~~"),A:B,2,false)– SO AnalyticsCommented Jan 18 at 19:19
-
That formula applies to all tilde characters in the lookup value - it doesn't matter where they are in the text. You may have to provide more information if it's not working for you– Cpt.WhaleCommented Jan 18 at 19:33
-
@SOAnalytics, please don't provide context in a comment. Edit your post and add the context. In addition to the formula you tried, please also provide sample data and tell us what your expected results are.– FlexYourDataCommented Jan 18 at 23:41
Add a comment
|
1 Answer
With a table like this where the value you want to search for (D3) has tilde characters, you can use =VLOOKUP(SUBSTITUTE(D3,"~","~~"),A:B,2,FALSE) to force excel to search for literal ~ characters:
The output from the vlookup formula is orange
See Working with "~" in excel for more information
-
1The new XLOOKUP deals with characters literally by default:
=XLOOKUP(D3,A:A,B:B)– P.bCommented Jan 19 at 15:38