Vlookup

[Joe]

HI,for some reason it doesnt work for me today.
TABLE example:
Acc type
73010 ADMIN Travel cost
74000 ADMIN

the other is:
Trx Date Acc # Acc name
01/04/2006 73010-100-CAN-000 Customer DL - Canada#N/A
28/04/2006 74000-100-IRL-000 Customer DL - Ireland #N/A
30/04/2006 63615-100-IRL-000 Customer DL - Ireland #N/A
30/04/2006 63615-100-IRL-000 Customer DL - Ireland #N/A

Using the formula

=VLOOKUP(LEFT(B78,5),ADMIN!$A$1:$B$32,2,FALSE)

I get #N/A

Wot am i doing wrong?

 

[Pete_UK]

You get #N/A when using VLOOKUP is the sought value is not present in
the table - perhaps you might need to add some more codes, or check
that you don't have spaces etc at the end of them.

Hope this helps.

Pete

 

[Joe]

thanks, that is not the problem. is like the formula doesnt read the number
right. Maybe diff format??? how can i change this?
thks

 

[Joe]

Is there anything that I might be doing wrong that affects the way Vlookup
picks up the numbers? Ie number or text format

 

[Pete_UK]

By using LEFT in your formula, you are returning a string, but perhaps
the codes in your lookup table are numbers. Try this:

=VLOOKUP(VALUE(LEFT(B78,5)),ADMIN!$A$1:$B$32,2,FALSE)

Hope this helps.

Pete

 

[Joe]

tks. it worked
Rgds,
jose

 

[Pete_UK]

Thanks for feeding back - glad it worked for you.

When you said this in your first post:

"... for some reason it doesnt work for me today ..."

I assumed that it had worked for you before today, but obviously not.

Pete