VLookup - mystery!!

scouse

Board Regular
Joined
Jan 16, 2004
Messages
207
Can anyone help I am tearing my hair out.

I have a number of workbooks which contain codes (e.g. 1 represents Germany, 2 England etc) and have done a series of vlookups which have all run perfectly well.

Except for one!

On workbook mop in columns A and B
A Cash
U Unguaranteed Cheque
E Deferred Account
D FACT Account
L Long deferred
X Exempt

The VLOOKUP code on my main worksheet is:

=VLOOKUP(AD831,MOP2!A:B,2)

[The code A, U etc is contained in the col AD in the main worksheet]

This is working for everyone of these which occur except it is giving me U as Long Deferred.......I have trying reformatting, retyping, taking the code table into a new worksheet but to no avail.

btw when i change the codes to
1 Cash
2 Unguaranteed Cheque
3 Deferred Account
4 FACT Account
5 Long deferred
6 Exempt

There is no problem...........

Yours confusedly,

Scouse
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
scouse

Try this:
=VLOOKUP(AD831,MOP2!A:B,2,0)
 
Upvote 0
Sorry lads - just realised that I had not sorted in ascending order. Apologies to all.....
 
Upvote 0
Sorry lads - just realised that I had not sorted in ascending order. Apologies to all.....
If you try the formula I suggested, you won't need to sort.
 
Upvote 0
Peter - thanks for that. Why does that 0 mean? How does it override the need to sort?
It causes the VLOOKUP to look for an EXACT match rather than an APPROXIMATE match.

However, I think the best place to look for a start is in the built-in Excel Help. Do a search for the VLOOKUP function and read about the various arguments. Note that 0 is equivalent to FALSE in the formula syntax.
 
Upvote 0

Forum statistics

Threads
1,224,245
Messages
6,177,400
Members
452,774
Latest member
Macca1962

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top