Index Match Not Working

[Moxioron]

Hello.

I have done this a thousand times before (well maybe hundreds) w/o issue. However, for some reason I can get anything to return on the following:

=INDEX('Flood Report'!$X$2:$X$15000,MATCH($A22,'Flood Report'!$AN$2:$AN$15000,0))

If I do a CountIf('Flood Report'!$AN$2:$AN$15000,$A22), it comes back with a 1 ... so I am not sure where I'm going astray.

Any help would be appreciated. Thank you.

 

[Toadstool]

I'd start by taking out the INDEX and see if MATCH gives you a row number. If it does then visually check $A22 with the AN column for that row.

If it looks OK do a =LEN($A22) check with that =LEN($ANnnn) cell to check you don't have hinky characters in either.

 

[marshy3300]

Agreed, the formula isn't the issue. Something is wrong with what you are referencing.

 

[Fluff]

Another thing to check, is make sure that there are no error values in col X

 

[Moxioron]

Thanks to all that responded. I did the following to both columns and it worked. Weird, I have never had this happen prior. Thanks again.

1) Highlight the column of data
2) Data > Text To Columns > Delimited > Uncheck everything > Next
3) On page 3 of the wizard, set the Column Data Format = General
4) OK

 

[Fluff]

Glad you sorted it & thanks for the feedback.

If you are looking up numbers, it sounds as though they were originally text.