Vlookup- truncate?

  • Thread starter Thread starter Mary
  • Start date Start date
M

Mary

I have a spreadsheet using VLOOKUP. Everything works all
my formula. Pulling info from 3-4 spreadsheets. Now I am
just doing some clean up work.

On one spreadsheet my P.O. # is like 5466 and on another
it is like 5466-Acme.

Is there a way to look up and match only the 5466 part?

This is all I need to finish.

Thanks for any help. Thanks Frank for the help you gave
me before. I know a lot about VLookup now.

Mary
 
Hi Mary

if your PO numbers are definitely all four digits long followed by the - and
whatever you can use this formula (assume PO number is in D3)

=VLOOKUP(VALUE(LEFT(D3,4)),lookup_table,col#,true_or_false)

Hope this helps
Cheers
JulieD
 
I don't have the time to try the formula for now. I will
probably do this early am like 1 am. Anyway, am I
correct in assuming that the 4 is the number of digit in
my PO. If I have 5 and now that I look at them I do, then
do I just change the (d3,4) to (d3,5)?

Thank you very very much.

Mary
 
Hi Mary

correct - D3 is the location of the PO (so change to suit) and 4 is the
length that you want to extract.

Cheers
JulieD
 
This is the formula, I tried but I got #NA. What am I
doing wrong?

=VLOOKUP(VALUE(LEFT(B2,5)),[pocomplete.xls]Sheet1!
$A$2:$I$996,6,0)
 
Hi Mary

some ideas -

ensure that there is somethign in cell B2
and that B2 matches the data in the other workbook A2

if you're using version 2002 or 2003 could you also try using the evaluate
formula option to see where the error is occuring - tools / auditing /
evaluate formula

Cheers
JulieD


Mary said:
This is the formula, I tried but I got #NA. What am I
doing wrong?

=VLOOKUP(VALUE(LEFT(B2,5)),[pocomplete.xls]Sheet1!
$A$2:$I$996,6,0)
-----Original Message-----
Hi Mary

if your PO numbers are definitely all four digits long followed by the - and
whatever you can use this formula (assume PO number is in D3)

=VLOOKUP(VALUE(LEFT (D3,4)),lookup_table,col#,true_or_false)

Hope this helps
Cheers
JulieD




.
Click to expand...
Click to expand...
 
Back
Top