VLOOKUP Problem

[akalien]

Hi,

I've got a problem with VLOOKUP.

In one worksheet I have pilotes with the race number and the club and in a second worksheet I have the results.

So when I type the race number in my second worksheet the name of the pilot should be returned but that's not the case for number 0 and 1

If I understood well VLOOKUP is not able to work with a 0 (zero) but I really need this 0 and how could this problem be solved

Many thanks for your help

[NBVC]

You will see that the 1 in the main sheet A4 is actually entered as text, if you select the cell the little green triangle will show a pop-out where you can select to convert to number...

You can also update formula to:

=IF(B11="","",VLOOKUP(B11,Pilotes!$A$2:$C$221,3,FALSE))

to remain blank until you fill in column B.

[Ace_XL]

The problem is to do with the cell format. I have changed it to'general' and now it picks all values! See attached

[Fotis1991]

Change the format in pilotes A4 to general!

[akalien]

Great, this works perfectly.

But what can I do to be able to work with a 0?

[ben_hensel]

The "1" is stored as text. Change it to a number and that is fixed...

The formula in the first cell, B11, starts looking from Pilotes!A4, not A2... the zero value you are looking for is excluded from the VLOOKUP()!!!

You could also use...

Please Login or Register  to view this content.

instead, it will do the exact same thing in another direction.

[NBVC]

Great, this works perfectly.

But what can I do to be able to work with a 0?

0 is bringing up Albert Phillippe. Isn't that what it should be?