I guess changing the formula to
=VLOOKUP(A1,A5:B8,2, FALSE)
will work fine.


gedburnell yazmis:
I'm creating a spreadsheet that will return the full name of aBeer
product by entering a four character abbreviated code.

See example below:
A B
1 W40Q
2
3
4
5 JSSK JOE BLOGGS BITTER
6 W40Q ASDA DRAUGHT BITTER
7 W30C 3.0% ASDA BEST BITTER
8 W40C 4.0% ASDA BITTER

=VLOOKUP(A1,A5:B8,2, TRUE)

When I enter W30C in cell A1, I should get "3.0% ASDA BEST BITTER",
but instead I get "JOE BLOGGS BITTER".

Could anyone please explain why? Am I doing something wrong with
VLOOKUP?

How do I incorporate built in functions into VBA too?

The problem is with the "TRUE" selection.
It means to search for the "closest" match instead of "exact" match.
I've never had a use for "closest"... so I always use "FALSE".

I tested it, and it worked just fine in your case.

As for use in VBA:

use: Application.WorksheetFunction.Vlookup(......)
as in:

NumberOfRows = Application.WorksheetFunction.CountA(Sheets
(ShtName).Range("A1:A65000"))

Yes the problem is with the "TRUE", but it does not return the "closest
match". To quote the help:

"If TRUE or omitted, an approximate match is returned. In other words, if an
exact match is not found, the next largest value that is less than
lookup_value is returned."

VLookUp is a top-down search and stops as soon as there is an exact match or
a next largest value to return.

As the help says:

"If range_lookup is TRUE, the values in the first column of table_array must
be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE;
otherwise VLOOKUP may not give the correct value."

In practice, the "TRUE" form of the lookup only tends to be used for picking
factors out of numeric tables, where each factor covers a range of input
values. The help for VLookUp has such an example. I reckon I'd use at
least 100 "FALSE" lookups for each "TRUE" lookup.

The column you are searching in (A) must be sorted in ascending
order. The Vlookup wizard tells you this.
I'm surprised it doesn't settle for the first entry since your Find
text "W30C" preceeds it alpha-wise. Sort the whole table and try
again. If other things preclude sorting, put a copy elsewhere and
sort the copy.

Your example was confusing (contents of A1 vs. your words). I
also didn't look at the Vlookup formula carefully, but the sort is
still needed.

The "W4" in the list (row 6) is 'jigher than' the "W3" you are
searching for, so it picks the previous "J". It doesn't pick the next
higher, but the one *before* it "passes up" the potential match
location (in the sorted list).