vlookup and index match issues

[hello12345]

Hi! I'm successfully using vlookup to return the city names for all the NHL hockey teams:

Boston Bruins | Boston
Dallas Stars | Dallas
etc.


HOWEVER, I'm having issues when it comes to the 2 New York teams. The formula for "New York Islanders" almost always pulls "NY Rangers", when what I really want is "NY Islanders"

New York Islanders | NY Islanders
New York Rangers | NY Rangers

Many people have recommended index match, but I have the same issue.
People have also recommended I put "FALSE" behind the formula for it to be exact match, but that just returns "N/A"

Here are the formulas I've tried:
=VLOOKUP(C91,$B$1:$C$33,2)
=INDEX($C$2:$C$33,MATCH(C106,$B$2:$B$33,2))


Any help is appreciated! Thanks!

 

[RobP]

Hi, I think its down to your match (type), which is essentially your "MATCH(C106,$B$2:$B$33,2))"

• match_type - [optional] 1 = exact or next smallest (default), 0 = exact match, -1 = exact or next largest.

Try using a 0 instead of 2 in there ?

Also, for your VLOOKUP, you can add another argument to the end like thus:

=VLOOKUP(lookup_value, table_array, column_index_num, [range_lookup])

[range_lookup] should be FALSE for an exact match.

Rgds
Rob

 

[hello12345]

Thank you - when I used "False" for VLOOKUP I get a "N/A"

=VLOOKUP(C101,'Short Forms'!$B$1:$C$33,2,FALSE)

When I use a 0 instead of 2 for index match I also get a "N/A"

 

[RobP]

sorry, you did say that for Vlookup() above, apologies.

maybe share some actual data in order we can see what we're playing with here in terms of names ? (using the tool we have "XL2BB" to help you)

 

[awoohaw]

what values are in C91, and C106?

If they contain "New York Islanders" or "New York Rangers" your formulas should work. But if they just say "New York" you will only get the first instance.

What do you do mentally when you objectively hear the words "New York", what do you do to distinguish between the two teams? You cannot, right? So, how can you expect Excel?

What tiebreakers can you think of? Maybe Long Island vs Manhattan?