Calc: VLOOKUP not returning result-no error message

asked 2019-07-12 18:15:31 +0200

Cantagril gravatar image

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.

edit retag flag offensive close merge delete

Comments

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))

Opaque gravatar imageOpaque ( 2019-07-12 18:25:48 +0200 )edit

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.

Cantagril gravatar imageCantagril ( 2019-07-12 20:32:59 +0200 )edit