LOOKUP Formula not returning correct answer

**preferiti** · 04-11-2009, 10:14 PM

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.

=LOOKUP(E4,{"Baltimore","New York Y","Tampa Bay","Toronto","Chicago","Cleveland","Detroit","Kansas City","Minnesota","Los Angeles","Oakland","Seattle","Texas","Atlanta","Florida","New York M","Philadelphia","Washington"},{"1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18"})

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

"(CONCATENATE(Scores!J30,"-",Scores!J31))

Hopefully this is solvable.

**NBVC** · 04-11-2009, 10:18 PM

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:

=MATCH(E4,{"Baltimore","New York Y","Tampa Bay","Toronto","Chicago","Cleveland","Detroit","Kansas City","Minnesota","Los Angeles","Oakland","Seattle","Texas","Atlanta","Florida","New York M","Philadelphia","Washington"},0)

**preferiti** · 04-12-2009, 03:27 AM

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.

=MATCH(E4,{"Atlanta","Baltimore","Chicago","Cleveland","Detroit","Florida","Kansas City","Los Angeles","Minnesota","New York M","New York Y","Oakland","Philadelphia","Seattle","Tampa Bay","Texas","Toronto","Washington{"(CONCATENATE(Scores!J74,"-",Scores!J75)","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18"})

Is there another way?

**TonyB51** · 04-12-2009, 04:11 AM

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** · 04-12-2009, 06:24 PM

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.

**daddylonglegs** · 04-13-2009, 06:26 AM

Originally Posted by 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.

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?

**Bob Phillips** · 04-12-2009, 06:57 PM

Originally Posted by 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.

=MATCH(E4,{"Atlanta","Baltimore","Chicago","Cleveland","Detroit","Florida","Kansas City","Los Angeles","Minnesota","New York M","New York Y","Oakland","Philadelphia","Seattle","Tampa Bay","Texas","Toronto","Washington{"(CONCATENATE(Scores!J74,"-",Scores!J75)","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18"})

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** · 04-12-2009, 08:35 PM

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** · 04-13-2009, 05:32 AM

Originally Posted by 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.

Then use two separate lookups and concatenate those.

LOOKUP Formula not returning correct answer

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

LOOKUP Formula not returning correct answer

Re: LOOKUP Formula not returning correct answer

Re: LOOKUP Formula not returning correct answer

Re: LOOKUP Formula not returning correct answer

Re: LOOKUP Formula not returning correct answer

Re: LOOKUP Formula not returning correct answer

Re: LOOKUP Formula not returning correct answer

Re: LOOKUP Formula not returning correct answer

Re: LOOKUP Formula not returning correct answer

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions