Can't use VLOOKUP on unsorted data

[dempa]

Hi!

I’m having trouble using VLOOKUP on my unsorted data (see this picture: Imgur: The magic of the Internet).

The list in A18 is created using Data → Validity → Cell range (A2:A14).

As you can see from the selected G18 cell, I’m trying to fetch the 6th column from the row in the upper left table where the first column (Produktnamn) matches the content of the A18 cell, which in turn is a value from A2:A14 due to it being a dynamic list. However, this still results in #N/A, even though there’s obviously a match somewhere in the table (in this case the 7th row).

If I sort the table (Data → Sort ascending), the VLOOKUP fetches the data just fine (if I also remove the last VLOOKUP parameter). If I don’t sort it, I get #N/A even though I’ve supplied FALSE for the ‘sorted’ parameter. How do I use VLOOKUP using an unsorted table?

[erAck]

As you have parentheses () in your search expression, make sure that “Enable regular expressions in formulas” under Tools → Options → Calc → Calculate is switched off and either “Enable wildcards in formulas” is selected (compatible with MS-Excel) or “No wildcards or regular expressions in formulas” if you also use the characters *, ? or ~ in your search entries for VLOOKUP.

[dempa]

Sorry for the late reply! Your solution fixed my problem, thank you very much! Did this problem occur because some products contained special characters such as ‘%’ in their names?

[erAck]

No, because they contain () parentheses, which are regular expression metacharacters so the search expression doesn’t match the data.