Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

I'm creating a spreadsheet that will return the full name of a Beer

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?

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

Didn't find what you were looking for?
Find more on Wanting to use VLOOKUP, but it returns unexpected results!
Or get search suggestion and latest updates.

Related Topics:

- Vlookup returns #NA
- VBA Vlookup returning wrong values
- using vlookup in userform
- Vlookup in vba when lookup fails
- Newbie VLOOKUP with dates question
- Help needed in VLOOKUP
- vlookup and error
- vlookup and the value #N/A
- VLOOKUP in VBA
- VLookup in VBA
- Vlookup in VBA
- Problem with VLookup in VBA
- vlookup with two imputs
- How to make WorksheetFunction.MATCH OR .VLOOKUP work?
- Help required in Vlookup
- VLOOKUP Use in Macros
- Perform a function based on a VLookup
- vlookup with two imputs
- Vlookup between two sheets with in a workbook to pick latest value via VBA
- Writing Macro wanted Tips
- Free pop3 component wanted
- J2ee hosting wanted
- trying to return the computername using applet
- Help with vlookup in vba, how to deal with errors?
- Add COUNT result to Titlebar