Look Up Errors

[BruzerQF]

Could you look at this excel sheet and see if you can what I am missing?

Blue cells are NFL game scores

Red cells are the list of Visiting teams, followed by the list of Home teams
The column “G” in red is an IF/THEN to find out if that team won or lost.
All of that is working like expected

Then Column A & B is where I am having trouble.

“A” is a list of all NFL teams in alphabetical order
“B” I want to be a look up field, Look in “A” find the match in “F20-F45” give me the value of “G”.

It works for some teams and not others. It has value for team that are not in the look up list because they are off.

Any ideas?

Col “C” is if they are home or visiting I thought that had something to do with it
Col “D” is the correct value that should be in ”B”

Book3.xls

[FlameRetired]

Try this formula in column B

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[FlameRetired]

You could also use

Please Login or Register  to view this content.

The reason your first formula gave incorrect returns is because the form of LOOKUP you were using performs an approximate match.

The VLOOKUP above would do the same thing if you were to change the last argument from 0 (or FALSE for exact match) to 1 (or TRUE for approximate match).

The same holds true for the last argument in the MATCH function in this post.

[FlameRetired]

You could also use

Please Login or Register  to view this content.

The reason your first formula gave incorrect returns is because the form of LOOKUP you were using performs an approximate match.

The VLOOKUP above would do the same thing if you were to change the last argument from 0 (or FALSE for exact match) to 1 (or TRUE for approximate match).

The same holds true for the last argument in the MATCH function in this post.

[BruzerQF]

Thanks The VLookup worked as i needed thanks

[FlameRetired]

You're welcome. It was my pleasure.