Using Vlookup to look up a value

[HowardC]

I have used the following formula to look up the account number in col B and to give me the value of the account number which appears in workbook BR1TB. It gives me a value #N/A.

=VLOOKUP(B3,BR1TB.xls!$A:$E,5,1)

For eg Acc # 102535 should give me a value of -3470.42

Attached please find sample data

It would be appreciated if someone could assist

[RetiredGeek]

Howard,

The reason the VLookup doesn't work is the formula you have in Col B. of the NV Sales sheet in the Account Numbers.xls file generates a TEXT value {i.e. they are preceded by an apostrophe ('102535) and the account numbers in the BR1TB.xls file are numbers. If you remove the apostrophe it works just fine as shown below.

Another way to fix the problem is to change your VLookup function as follows:
=VLOOKUP(VALUE(B3),BR1TB.xls!$A:$E,5,TRUE)

[HowardC]

Thanks for the help. I will use your formula which I like

[rory]

FYI, unless you can guarantee that the account numbers appear in both lists and that the source table is sorted in ascending order, I would recommend you use:
=VLOOKUP(VALUE(B3),BR1TB.xls!$A:$E,5,FALSE)

[RetiredGeek]

Rory,

Thanks, you're exactly right. I was a little sleepy when I wrote that.