LOOKUP Formula not returning correct answer

[preferiti]

Ok I’m going to try to ask 2 questions and hopefully they are answerable and doable.

Once again I’m at it again with my sox spreadsheet. What I’m trying to do now is I have a drop down menu that allows me to pick any team that we can face in the season. Once that is chosen what I want to show is the individual stats against that team. Now right now I’m just trying to work out the formula to see if it works.

The formula itself is half working. For some reason some of the selections aren’t showing up like there suppose to do.

Here is the formula I’m trying to work.

Please Login or Register  to view this content.

Now each team should have its own number but when I select Florida it comes up as 1. Am I not seeing something?

And if I’m not seeing it once it's corrected, Can I make it bring up two cells from a different sheet by using the CONCATENATE formula? Like

Please Login or Register  to view this content.

Hopefully this is solvable.

[NBVC]

The LOOKUP() function requires that your lookup list be in ascending alphabetical order.. which your list is not...

but since you are returning numbers that happen to be based on position of your lookup value within the lookup list, you can use Match() instead.

Try:

Please Login or Register  to view this content.

[preferiti]

thanks for the advice on the Match. btw, i took your advice to make life easier, i put them in alpha order. BUT, (lol) it's not letting me combine two cells together like i want it. Is it not possible or is it and i'm just (still)stupid?

I'm trying to do this.

Please Login or Register  to view this content.

Is there another way?

[TonyB51]

Hi,

I am not using 2007 but 2003. The attached amendment should still work for you.

I believe using a table for lookups works better, particularly when you need to add or delete plus sort.

See what you think.

Cheers

Tony

[preferiti]

I think i'm not explaining enough. what i want is to have each cell "Home" "Away" "Total" "PCT" show the respected information from each team. The information i need to pull is on Scores Colum J.

The numbers what just a test on how to do it.

sorry for the confusion.

[Bob Phillips]

thanks for the advice on the Match. btw, i took your advice to make life easier, i put them in alpha order. BUT, (lol) it's not letting me combine two cells together like i want it. Is it not possible or is it and i'm just (still)stupid?

I'm trying to do this.

Please Login or Register  to view this content.

Is there another way?

In this form, you should use LOOKUP not MATCH.

If Baltimore is the only one to get that concatenantion, do an IF test for Baltimore, and exclude them from the LOOKUP.

[preferiti]

Baltimore isn't the only one to use the concatenantion, I just didn't want to write all of the formula's if it woudn't work.

[Bob Phillips]

Baltimore isn't the only one to use the concatenantion, I just didn't want to write all of the formula's if it woudn't work.

Then use two separate lookups and concatenate those.

[daddylonglegs]

I think i'm not explaining enough. what i want is to have each cell "Home" "Away" "Total" "PCT" show the respected information from each team. The information i need to pull is on Scores Colum J.

The numbers what just a test on how to do it.

sorry for the confusion.

You say you want to get information from Scores column J. Can you explain what you mean. In your example what should the result be in F4 and why?

[preferiti]

Then use two separate lookups and concatenate those.

How would i go about that?

You say you want to get information from Scores column J. Can you explain what you mean. In your example what should the result be in F4 and why?

Well F4 would depend. If F3 is Tampa Bay Rays than F4 would be "1-2". If it was "Oakland Athletics" it would be "0-0."

See what i mean?

[preferiti]

should i close the ? or...?