Excel 2007 : VLOOKUP after Extracting

[SCLai]

Sorry for the bad title... but this is basically what happened:

---- ---- A ---- ---- ---- B ---- ---- ---- C ---- ---- ---- D
1 | 319R25201
2 | 783RG1720
3 | 319R25302
4 | 320R0G037

Basically, I would like to:

- Extract the 1st 3 char from Column A (E.g 319 from 319R25201)

- Compare it to a pre-defined table, E.g: Column F & G
-- ---- ---- F ---- ---- ---- G ----
1 | ---- --319 ---- ---- SP-01
2 | ---- --320 ---- ---- SP-02
3 | ---- --780 ---- ---- PA-12
4 | ---- --783 ---- ---- PA-15

- And return the value of Column G into column B

I have tried =VLOOKUP(LEFT(A1,3),F1:G4,2,0), but it returned #NA.
If i input 319 directly into VLOOKUP. E.g =VLOOKUP(319,F1:G4,2,0), it works fine.

Thanks in advance!

[ConneXionLost]

Hi,

It's likely the result of the LEFT function needs to be converted to a numerical value. Try this:

=VLOOKUP(VALUE(LEFT(A1,3)),F1:G4,2,0)

Cheers,

[SCLai]

Wow thanks its working fine now!
I have tried "TEXT" but obviously it didnt work -_-"
Thanks for the help again!