[Solved] VLOOKUP with different spellings of the same thing

[chilli76]

Hi guys

I use the formula below to get some values from another sheet that pertain to certain football teams where Z6 is the name of the football team on the sheet I am using the formula. The problem I have is if the name of the team on the sheet I am getting the values from (Prices.K20) is slightly different (Manchester United / Man Utd) due to using different data sources I get a lot of N/A's. The formula is fine as it works when the team names match. Can I include something to help the formula recognise the two as being the same?

=VLOOKUP(Z6;Prices.K20:N90;4;0)

Cheers

[Richarda44]

Computers have to be told exactly what you want to lookup. We complain when they guess wrongly what we want.

Could you include duplicate lines in the lookup table for each name variation?

Richard

[chilli76]

Not Sure what you mean tbh.

I can make a table on the first sheet with the variations next to each other eg

Manchester United | Man Utd
Tottenham | Tottenham Hotspur
Stoke | Stoke City

with the left column being the first sheets variations and the right for the 2nd sheet but how do i incorporate that into the formula?

[Richarda44]

I meant increasing the original lookup table Prices.K20:N90 to include all the name variations.

[chilli76]

Can't I don't think. The Prices.K20:N90 is imported data from a website which I want left as is so i have minimal input in terms of workbook maintenance. The idea is that the external link on Prices sheet updates the cell values in what I call Entry 2 sheet, namely the odds for the matches. Like I said it works fine for teams that match spelling wise just can't sort out the ones that don't.

[Richarda44]

In that case what you said above - Make a mini lookup table with every name variation and nest the "correct" result as a substitute for Z6

[chilli76]

I've made a table but I don't know what you mean by nest the correct result.......forgive me for been dumb

So in Entry 2 Z6 i have Botafogo but on the prices sheet it is referred to as Botofogo RJ

I have a table now on my Entry 2 sheet

Botafogo | Botafogo RJ

in cells AG24 and AK24 respectively

How then do I get my look up from my original formula to recognise Botofogo as Botofogo RJ?

Thanks for your help by the way.

[acknak]

Another approach: use a formula to normalize the key column. So if the team name contains "Manchester U", then the key is "manchester" and vlookup can use that.

Or, VLOOKUP can use regular expressions, so you can use a pattern/wildcard as the lookup key.

But if the data are unreliable, you've a steep hill to climb.

If you want specific suggestions, please attach a small sample sheet that uses your layout & data.

[Lupp]

A slightly different approach (using SEARCH insted of a second LOOKUP() nested in another expression) you find examplified very raw in the attached document.
As I wanted to have the team names (variants) in one row per team I had to use a trick getting a 1-dim array for the matching.
Please note that SEARCH is not case sensitive (neither are VLOOKUP or MATCH)!

An unsolicited advice: Don't nest much. Better use helpers. IMO this much simplifies maintenance.

(PS The 10th variant for the same team should be entered with a "§" suffixed (or whatever character you placed into M10). An alternative is, to use column W for concatenating this suffix to the preliminary string.)

[chilli76]

Thanks for your help guys. It might be best to attach a sample. It's all getting a bit complicated for me

The highlighted red sections are where the formulas work perfectly because the team names match. The rest doesn't because they don't. The lookup table shows the 2 variations of the spellings but I don't know how to refer one to the other if you see what i mean.

All help willingly accepted.

Cheers

Ps I had a devil of a time getting the file size down so it's stripped of pretty much everything

[Lupp]

OK. It's my responsibility, of course. Nonetheless I'm not happy with this forum software which the second time within a few months threw me out when I had written a thoroughly explaining response switching to the log-in page without a warning that I would loose the written text. A solution for this issue (except looking in advance if logged in every time)?
Thus without an explanation now a second example. It's about a concept. It's not a solution for the complete task.

[RoryOF]

A long or complex reply, which may take time (greater than 15 mins? I'm not sure of the auto-logout tme) to prepare is perhaps best written in an external editor then Copied/Pasted into the Forum's reply window.

[Lupp]

Thanks, "RoryOF".
I had a break more than once and don't know the time.
What you suggest was in my mind more than once, too. It's a question of discipline. I think ... no problem ... avoid overhead ... get interupted ... . Maybe I'm too mild with an old man's lack of strictness? Another thing is practicability. If I write in the forum's editor I can easily go back to another post in the thread ... copy a piece for quoting ... help my bad memory ... use my online dictionary without changing the application (I'm German and got a C- in English last examined 51 years ago )... .
I already emphasised my own responsibility. Nonetheless it would be great to get a warning on clicking the 'Preview' or the 'Submit' (or 'Attach'?) button instead of being forewarded to the login page completely replacing the editor - and thus loose the text irretrievably. Changing from "not logged in" to " logged in" might also be managed without loss of edited text by delegating the login to another tab/window. Are there still many browsers in use not capable of this?
Or is there remedy after the slip? I tried the 'Back' button of my browser, of course.

[acknak]

Firefox saves my typing when this happens. I can (almost) always use the back arrow to get back to the page where I was typing and then re-submit after logging in again.

[RoryOF]

acknak, you and I are moderators and may have special privileges. I also use continuous login ("Keep me logged in"), which may also affect the Forum behaviour for me.

Edit: A workaround occurs to me - upload the posting after say 10/12 mins with an "In Progress" last line. Then edit it, saving after say 10 mins and again edit until finished. Clunky, but ...

[Alex1]

You can insert columns between K and L and between L and M on the Prices sheet. Here you look up the normalized names from a table of spelling variants.
The result can be used in your calculations, e.g. the formula in your opening message would be
=VLOOKUP(Z6;Prices.L20:P90;5;0)

[chilli76]

Cheers Alex, that seems to have done the trick. I'm having trouble with one team name but the rest are fine. I'll see if I can figure out why just the one is returning #N/A

[acknak]

Does the problem name contain any punctuation characters: . * +, etc? These may be interpreted as pattern-matching symbols, which interfere with the lookup.

[chilli76]

Does the problem name contain any punctuation characters: . * +, etc? These may be interpreted as pattern-matching symbols, which interfere with the lookup.

I can't see any? The name in question is Santos (n) where the vlookup doesn't recognise Santos (n). Do the brackets have something to do with it?

[chilli76]

Sorry should he checked the tutorial before spouting off It seems the brackets are cause for consternation.

[acknak]

... It seems the brackets are cause for consternation.

The brackets have a special meaning--they're ignored, essentially. It matches Santos n (without brackets) but not Santos (n).

You can either "quote" the brackets to prevent the special treatment: Santos \(n\)

Or (in current versions of AOO) you can quote them in the lookup formula: VLOOKUP("\Q" & A1 & "\E"; ... )
to disable all special pattern matching characters.

[chilli76]

Many thanks everyone for your kind help. It all makes a bit more sense now.

[chilli76]

Hi acknak

... It seems the brackets are cause for consternation.

The brackets have a special meaning--they're ignored, essentially. It matches Santos n (without brackets) but not Santos (n).

You can either "quote" the brackets to prevent the special treatment: Santos \(n\)

Or (in current versions of AOO) you can quote them in the lookup formula: VLOOKUP("\Q" & A1 & "\E"; ... )
to disable all special pattern matching characters.

Would the above addition to a VLOOKUP function work to rid the end product of an apostrophe ['] included in imported web data?

I know that the result of the formula VLOOKUP(AA3;Prices.L15:U85;10;0) gives me the right values but another sheet reading these values has trouble with the ' that is in the imported data. Where would I put your suggestion in the formula presuming it would work?

[acknak]

... Would the above addition to a VLOOKUP function work to rid the end product of an apostrophe ['] included in imported web data?

I don't think it would have any effect; that's a different issue.

... another sheet reading these values has trouble with the ' that is in the imported data.

Sorry, I'm not sure I understand what's going on there.

Where would I put your suggestion in the formula presuming it would work?

Wherever you use VLOOKUP and you want to make sure the brackets or other punctuation characters don't interfere with the matching.