Index/Match formula not recognizing a row.

[damianberry]

Hello there,

In AI563-567 and in AI572-576 I am tracking the opponents for scores in fantasy football. This formula is working fine, with one exception.

For some reason, only the row F206:V206 isn't being recognized to reference the opponent's name.

The following rows are being used to gather the names:
F5:V5
F55:V55
F105:V105
F155:V155
F206:V206
F256:V256
F306:V306
F356:V356
F407:V407
F458:V458

I got the formulas from someone else here at the forum. I don't understand why it won't work for that one particular row. I have excel 2003.

Thanks,
Damian

[Ace_XL]

Column A 'Keith E ' has trailing spaces which is causing the error.

Remove the trailing spaces and you'd be fine

[Moo the Dog]

It is because the value you are looking up doesn't equal the value in column A. You are trying to match "Keith E" in column A, but all of the cells that have "Keith E" in them are actually "Keith E " ... they have an extra space after the E. Change cells A207:A248 to "Keith E" (without quotes) and it should work fine.

- Moo

[damianberry]

Well, thanks to two guys who are much smarter than I am! How'd you guys figure that out?

[Moo the Dog]

Glad we could help.

The first thing I always look for when a lookup or index/match formula returns an error is whether or not there truly is a match. If I see a cell, or cells, in the lookup array that appear to match my lookup item, then there's a pretty good chance that something like formatting (date/time/text, etc.), rounding, or extra spaces is the culprit.

- Moo