excel help

[thechin]

Excel ??
--------------------------------------------------------------------------------
i am using a lookup function, b/c i have a table where i am pulling a value in column B after i match it with it's name in column X

B6 is the cell that i want to pull the team power rating from on my power rating sheet

POWER! is my power rating sheet

$X$22:$X$50 is the column with all nba cities (BOSTON, NEW YORK, NEW ORLEANS, ETC)

$B$22:$B:$50 is the column with the team power ratings

here is the formula i have:

LOOKUP(B6,POWER!$X$22:$X$50,POWER!$B$22:$B$50)


now the point of my post:

when it returns the value for cell B6,it is confusing NEW YORK AND NEW ORLEANS --but it is returning the correct value for NEW JERSEY

any idea why, and how i can i fix this ??


thanks

[rabbit]

Not sure why it is doing that, but I would simply eliminate the space between NEW and YORK (also NEW and ORLEANS), or add an underline, e.g. NEW_YORK.

Hope that helps.

[thechin]

thanks rabbitt

i use the current format to import my scores from don best, so i guess i will have to employ your method

wasn't sure if excel only read the 1st 3 characters, but it was returning the correct value for new jersey -- so it can't be that -- for new york it keeps returning the new orleans value


will try your suggestion


thank you

[Machiavelli]

Two solutions:

1) Sort your power-rating list alphabetically. LOOKUP needs a sorted list. I think that when Charlotte moved to New Orleans, it might have messed you up.

or

2) If you want to be able to lookup in a non-sorted list (and return an #ERROR when you don't get an exact match), you need to use a combination of the MATCH and INDEX functions.

Instead of your LOOKUP formula, use:

INDEX(POWER!$B$22:$B$50,MATCH(B6,POWER!$X$22: POWER!$X$50,0))

(That is a zero in bold. Also, you don't need a space between the colon and the POWER!$X$50, this message board reads a ":" and a "P" as a smiley face)

The MATCH function, when used with a zero as the third parameter, will return the relative position of the first exact match of an array. For example, if "NEW ORLEANS" was in cell B6, and "NEW ORLEANS" was the fifth team on the list, then MATCH will return 5.

Then, in this case, you feed the 5 into the INDEX function, which will return the fifth value in the list of ratings.

If the match isn't precise, you will get an error. I find this to be a more reliable setup, I think you want to know that there is an ERROR when something isn't exactly right.

Hope this helps. See also MS Excel help for MATCH and INDEX.