VLookup returns #N/A

[FlynnLOL]

Hello there,

I'm trying to do a VLookup in Excel VBA but it doesn't seem to be working properly. Here's the setup:

In a worksheet named "Customers" I have a table of the customer's ID number (Column A) and his/her name (Column B) and other details in the following columns (eg. State in column C, etc.)

In a UserForm, I have a listbox with all the customer names and a button called "Retrieve" which when clicked will retrieve the customer's ID number and display it in a MsgBox. However, the VLookup never seems to work properly despite working when I use another lookup value such as the customer's state.

The code giving me an error (#N/A) is as follows:

Dim cusNo As Variant

cusNo = Application.VLookup(lstCustomer.Value, Sheets("Customers").Range("A2").CurrentRegion, 1, False)

MsgBox CStr(cusNo)

lstCustomer is the listbox's name and the customer's ID number is in column 1.

I've been at this for 30 minutes and I can't figure out why. Customer names are in the format of [first name] [last name] such as Jim Smith. When I try to do the VLookup in reverse, using the customer's ID number as the lookup value, it works:

cusNo = Application.VLookup(Val("004"), Sheets("Customers").Range("A2").CurrentRegion, 1, False))

Will properly return a value.

The stranger thing is, on a separate userform where I coded a button to retrieve company product prices, I used the exact same codes and methods (with appropriate variables changed) and it works.

Feel free to ask for clarification and such. Thanks

 

[VoG]

VLOOKUP will only look to the right of a lookup value. Maybe

cusNo = Application.Index(Application.Match(lstCustomer.Value, Sheets("Customers").Columns("A"), 0))

 

[FlynnLOL]

VLOOKUP will only look to the right of a lookup value. Maybe

cusNo = Application.Index(Application.Match(lstCustomer.Value, Sheets("Customers").Columns("A"), 0))

Thanks for the reply, but it seems to be giving me an "invalid number of arguments" error

 

[VoG]

Sorry. Maybe

cusNo = Application.Index(Sheets("Customers").Columns("A"), Application.Match(lstCustomer.Value, Sheets("Customers").Columns("B"), 0))

 

[FlynnLOL]

Sorry. Maybe

cusNo = Application.Index(Sheets("Customers").Columns("A"), Application.Match(lstCustomer.Value, Sheets("Customers").Columns("B"), 0))

Much thanks VoG, I actually just figured it out as I read this by looking up Index/Match functions myself

I actually was not aware that VLookup only looks to the right of the lookup value. That was definitely the case here.

I guess that's the kind of things you overlook when you've only been learning VBA for a couple weeks :P Anyways, thanks again mate :D