user1131153
user1131153

Reputation: 11

can I make a vlookup find a certain word in a cell?

What I am trying to do is.

=VLOOKUP(C7,MODEL!A$4:B$9975,2,FALSE)

For Cell C7--- Jacket Blue Men's

What I want it to do is use a Symbol for Men's

So my original sheet has the products all listed...this goes to another list and I want to make Jacket Blue Men's-------- | Jacket Blue | ♂ |

So Does Vlookup recognise (Men's) and will use the list to autofill another cell?

I tried true and it gave me a NA#

Thanks...this is the last step in a MASSIVE puzzle

NEW QUESTION

Can I only have one Vlookup command per sheet?

Also the current =VLOOKUP(B397,Sheet2!C3:D5,2) IS returning a ♂ but the specific word in that cell is Women's

So I guess I need to figure out how it will differentuate between women's and Men's. I have tried many formulas, and I get NA# or errors in the formulas.

Can anyone answer this or am I really stuck and have to add ♂ and ♀ and Ω to all 10k entries?

Upvotes: 1

Views: 10704

Answers (1)

barry houdini
barry houdini

Reputation: 46451

If you want "Men's" to always match with "♂" then try using SUBSTITUTE which will replace any instance of "Men's" in the lookup value with that symbol, i.e.

=VLOOKUP(SUBSTITUTE(C7,"Men's","♂"),MODEL!A$4:B$9975,2,FALSE)

...or do you need it to be | ♂ |....I'm not clear....but the principle is the same....

=VLOOKUP(SUBSTITUTE(C7,"Men's","| ♂ |"),MODEL!A$4:B$9975,2,FALSE)

Upvotes: 3

Related Questions