VLookup - mystery!!

[scouse]

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

 

[Peter_SSs]

scouse

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

 

[scouse]

Sorry lads - just realised that I had not sorted in ascending order. Apologies to all.....

 

[Peter_SSs]

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.

 

[scouse]

Peter - thanks for that. Why does that 0 mean? How does it override the need to sort?

 

[Peter_SSs]

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.