[Solved] Validity choice results in error in Vlookup

[morphingstar]

Validity choice results in error in Vlookup, though may work correctly.
An error or a bug?
Sheet1 holds the 1st design. Validity selection results in error in VLOOKUP
when rows 4 & 5 are wanted as a result, 1 of 2
There are 2 validity cells/menus for test comfort,
allowing to compare the results of choices.
_____ Sheet2 holds a copy of sheet1,
_____ cell ranges etc verified to stay in same sheet.
_____ In sheet2 the validity choice is processed as intended
_____ The result is correct.
Reliability problem with VALIDITY or VLOOKUP ?
Note: Validity sorting has no influence on operation.

tks for help
TAB has no effect, therefore _____

[morphingstar]

correction: Shhet2 C18 should read "5 work*

[RusselB]

Your lookup array isn't sorted in alphabetical order, so you need to enter the SortOrder parameter in your VLOOKUP statements and have it specified as 0 or FALSE
I recommend just entering a 0 as the last parameter, which, currently, you have missing.

[morphingstar]

This worked, thank you. So: either the Vlookup Array must be sorted to start with or the Validity setting must include Sort. Solved!

[Villeroy]

This worked, thank you. So: either the Vlookup Array must be sorted to start with or the Validity setting must include Sort. Solved!

I'm sorry to disagree. These two options are not equivalent.

0 A
1 B
2 C

=VLOOKUP( 1 ; $A$1:$B$3 ; 2) => B
=VLOOKUP( 1 ; $A$1:$B$3 ; 2; 0) => B
=VLOOKUP( 1.5 ; $A$1:$B$3 ; 2) => B (1.5 falls in category B)
=VLOOKUP( 1.5 ; $A$1:$B$3 ; 2 ; 0) => #N/A! (value not availlable)
=VLOOKUP( 999 ; $A$1:$B$3 ; 2) => C (falls in higest category)
=VLOOKUP( 999 ; $A$1:$B$3 ; 2 ; 0) => #N/A! (value not availlable)

In ordered mode (last argument missing or 1) you always get some return value for any search value unless the search value is smaller than the first value on the scale as in =VLOOKUP( -1 ; $A$1:$B$3 ; 2) . In ordered mode you ask for a matching category. in "database mode" (last argument =0) you ask for an exact match.

[RusselB]

In my opinion, Villeroy, this is not a fair comparison.
In order to make a fair comparison, please present your arguments based on the data as set in the OP's supplied spreadsheet, which has the search column in an unordered state.

[Villeroy]

Enter "xyz" into C18 or F18 The lookup matches at the "style" entry. The validation feature may prevent non-matching entries once it has been turned on. However, it is a misunderstanding that both operation modes are equivalent as long as you keep an ordered search vector.

[morphingstar]

thank you, I shall study these answers. The example uses completely different choices in the cells. However there could be something like aab, baa, bba, mp3, mp4, etc. The selection must be exact in the case shown. However, if there is a way to have an approximate selection, or similar not equal this may become useful in a different application. Like Contains "y".

[robleyd]

You may find [Tutorial] VLOOKUP questions and answers a useful resource - it will provide more information on VLOOKUP.