Ask Your Question

Calc: VLOOKUP not returning result-no error message

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

Cantagril gravatar image


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


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

1 Answer

Sort by » oldest newest most voted

answered 2019-07-16 11:56:28 +0200

Opaque gravatar image

updated 2019-07-16 11:57:52 +0200

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


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

Seen: 77 times

Last updated: Jul 16 '19