Value not shown in filter

[xybadog]

Hi

I have a curious issue that I've never come across with Excel before.

It all started with a column of data (Col A)and a VLOOKUP to find one specific value within that column. When it returned a #NA error I double checked.

So, a physical check of col A shows that the value is definitely there (Row 3719). However, when I filter the column it is not presented as an option with a check box. It's not there. And the VLOOKUP can't find it either.

I've tried a direct test by using

Please Login or Register  to view this content.

and it returned "Yes". So I'm not losing the plot but neither VLOOKUP, nor filter can find it. I've checked and changed formatting, converted text to columns etc etc but can't figure out why the value isn't shown in the filter.

Any able to advise me on this phenomenon please?

[Pete_UK]

You might be looking for an integer value, but it is actually a fraction with a very small fractional part.

It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon (Attachments button), as it doesn't work on this Forum.

Hope this helps.

Pete

[xybadog]

Attached. The value I'm looking for is in D1 (for the purposes of the VLOOKUP) but the value that's not returning in column A is in fact on Row 3749

Hope this makes sense.

[Pete_UK]

Some of the "numbers" in column A are proper numbers, but some of them (especially near the bottom of the list) are actually text values which just look like numbers. You can spot these if you widen column A - proper numbers will be aligned to the right by default, whereas text values are aligned to the left.

You can convert the values to proper numbers using Text-to-columns. Alternatively, you can change the formula in D2 to this:

=IFERROR(VLOOKUP(D1,A1:B3810,2,FALSE),IFERROR(VLOOKUP(D1&"",A1:B3810,2,FALSE),""))

Hope this helps.

Pete

[xybadog]

That works perfectly, thanks Pete.

[Pete_UK]

You're welcome - thanks for the rep.

Pete