"0" value instead of cell value in HLookup

[MrFMC]

I have an HLookup formula series in a sheet that looks up information on another sheet in the same workbook. The series goes from cell 2 all the way up to cell 57. The formula works fine until I get to the 21st cell. It then returns a "0" on cells that the reference data is a validation rule list selection. Cells where the HLookup value is a formula still return the correct data. No matter what I try and do, I can't find the reason or the fix. Any help would be appreciated. Thank you.

 

[par60056]

Can you post the formula? Did you lock the lookup range with $ before copying it?

 

[MrFMC]

Here's the formula. The first one works just right. The second one returns the "0".

=HLOOKUP($B$6,Tech!$B$6:$AY$62,12)
=HLOOKUP($B$6,Tech!$B$6:$AY$62,22)

They are both the same formulas and they both return the selection from a data validation list. Everything is the same about the source data and the formulas. I am totally confused.

 

[par60056]

so you are returning rows 17 & 27 They are both in range. Are they the right rows to return?

 

[MrFMC]

Yes. Like I said, it makes no sense to me. I tried resaving the workbook under a different name, closing the exisiting one and reopening it. I am not sure what is going on here. When I put a row number that does work in one of the previous formulas into the cell that returns the "0", I get the correct result. There has to be a problem with the source, but even when I try and change that, it still doesn't work, unless I get rid of the data validation. But, like I said, it works fine for the previous cells.