Vlookup

  • Thread starter Thread starter Larry
  • Start date Start date
L

Larry

Hey All,

Based on the data below, I'm looking for a formula that
will perform a lookup based on the first 6 numbers in file
Aug. and return either the full number or just the first
six numbers from July file if found and a text message if
not found, i.e. "NF". I'm pretty sure it might
incorporate a nested Left function?, but I've been unable
to resolve. I greatly appreciate the help.

Larry

Cells A1:A5 in file July contain the following:
852301.1
852306.2
852307.2
852309.3
852310.1

Cells A1:A5 in file August contain the following:
852301.2 ( Value returned would be 852301 or 852301.1 )
852306.3
852308.2
852309.1
852311.2 ( Value returned would be NF )
 
This may not be what you had in mind, but it's nice and
simple.
If you can you store Jul. data this way:
A2 = 852301.1 B2 = Left(A2,6)
Your Aug. file would only need:
A7 = 852301.2
B7 = VLookup(Left(A7,6),$B$2:$B$6,1,false)

Even better, store Jul. as:
A2 = Left(B2,6) B2 = 852301.1
and set up Aug. as follows: (gives the whole number)
A7 = 852301.2
B7 = VLookup(Left(A7,6),$A$2:$B$6,2,false)
 
Back
Top