Calc: VLOOKUP not returning result-no error message

[Cantagril]

Version: 5.1.1.3

My Spreadsheet contains two columns, one of which is a manually entered text string e.g ABCDE and the other a category, let’s say ALPHA, BETA etc… which I want to be displayed automatically.

I have created two further columns AC & AD) which contain the array with all the possible 5 letter strings and their respective categories.

My target cell formula is =VLOOKUP(C3;AC3:AD25;2;0) This doesn’t work and does nor return an error message, merely being displayed in the target cell. I also tried FALSE instead of the 0 to no effect.

I’m obviously doing something stupid but I just can’t see what.

[anon73440385]

Could you provide an anonymized file and upload here? Use the clip symbol, while editing your question for that purpose.

Just one hint in advance: Be absolutely sure that your search array does not contain any special unvisible characters, since an unsorted list (1st column of the array to search in) does only show any result, if there is an exact match - just a single blank in any of the cells (search or array) and there will be no match. And if there is no match and no error, then you would just get a blank cell. And this is exactly what you see.

For testing purposes you may use:

=IF(VLOOKUP(C3;AC$3:AD$25;2;0)=""; "No Match"; VLOOKUP(C3;AC$3:AD$25;2;0))

[Cantagril]

It works just like that!! I can’t understand why mine doesn’t but I’m in business and that’s what counts, so thank you very much again.

[anon73440385]

Just for the record:

=IF(VLOOKUP(C3;AC$3:AD$25;2;0)=""; "No Match"; VLOOKUP(C3;AC$3:AD$25;2;0)).

The $ characters assure absolute reference to the search array, so there is no shift while copying to the formula to cells in other rows.

Note: As per comment of OP this works for him.