How can I find items that have a certain prefix and define?

[Guest]

I have one file (file A) that has many items and these items all begin with
different prefixes(ex. aaa123456). I have another file (file B)that can
identify what the prefix means. This file B has the 3-digit prefix in
column A, the full name of the prefix in column B. How can I add a new
column in File A that has the full name of the prefix by creating a formula
to look at File A, and if it contains a prefix that is on File B, it will add
the corresponding name to that row?

 

[Roger Govier]

Not sure whether your File A and File B are separate sheets in the same
workbook, or separate XLS Workbooks.
Assuming they are on Sheet1 and Sheet2 of the same workbook, then in your
new column in File A (Sheet1) enter
=VLOOKUP(LEFT(A1,3),Sheet2!$A$1:$A$100,2,0)
Change ranges to suit.

If they are different workbooks, using Sheet1 in each book then you would
need
=VLOOKUP(LEFT(A1,3),[File B.xls]Sheet1!$A$1:$A$100,2,0)
Again substitute ranges to suit and your correct filename for File B

 

[Guest]

Roger, You are correct that I am using Sheet 1 and Sheet 2. Can you help
explain this a little more for me? In sheet 1 column A, is a number that
begins with 3 letters. In Sheet 2, Coulmn A is a list of the three letters.
In Sheet 2 column C is the full name that these three letters mean. How can
I get the full name of the 3 letters to show up next to item in Sheet 1 that
has the 3 letter prefix.

EX
Sheet 1, A2: AAC123456
Sheet 2, A2: AAC
Sheet 2, C2: Advanced Aero Client

Thanks, Ty

Not sure whether your File A and File B are separate sheets in the same
workbook, or separate XLS Workbooks.
Assuming they are on Sheet1 and Sheet2 of the same workbook, then in your
new column in File A (Sheet1) enter
=VLOOKUP(LEFT(A1,3),Sheet2!$A$1:$A$100,2,0)
Change ranges to suit.

If they are different workbooks, using Sheet1 in each book then you would
need
=VLOOKUP(LEFT(A1,3),[File B.xls]Sheet1!$A$1:$A$100,2,0)
Again substitute ranges to suit and your correct filename for File B

--
Regards

Roger Govier

I have one file (file A) that has many items and these items all begin with
different prefixes(ex. aaa123456). I have another file (file B)that can
identify what the prefix means. This file B has the 3-digit prefix in
column A, the full name of the prefix in column B. How can I add a new
column in File A that has the full name of the prefix by creating a
formula
to look at File A, and if it contains a prefix that is on File B, it will
add
the corresponding name to that row?

 

[Roger Govier]

Change the formula to
=VLOOKUP(LEFT(A2,3),Sheet2!$A$2:$A$100,3,0)

Note the 3 in this formula as opposed to the 2 as posted earlier. I had
thought your fullname was in column B of Shhet2 not in column C.


--
Regards

Roger Govier

Roger, You are correct that I am using Sheet 1 and Sheet 2. Can you help
explain this a little more for me? In sheet 1 column A, is a number that
begins with 3 letters. In Sheet 2, Coulmn A is a list of the three
letters.
In Sheet 2 column C is the full name that these three letters mean. How
can
I get the full name of the 3 letters to show up next to item in Sheet 1
that
has the 3 letter prefix.

EX
Sheet 1, A2: AAC123456
Sheet 2, A2: AAC
Sheet 2, C2: Advanced Aero Client

Thanks, Ty

Not sure whether your File A and File B are separate sheets in the same
workbook, or separate XLS Workbooks.
Assuming they are on Sheet1 and Sheet2 of the same workbook, then in your
new column in File A (Sheet1) enter
=VLOOKUP(LEFT(A1,3),Sheet2!$A$1:$A$100,2,0)
Change ranges to suit.

If they are different workbooks, using Sheet1 in each book then you would
need
=VLOOKUP(LEFT(A1,3),[File B.xls]Sheet1!$A$1:$A$100,2,0)
Again substitute ranges to suit and your correct filename for File B

--
Regards

Roger Govier

I have one file (file A) that has many items and these items all begin
with
different prefixes(ex. aaa123456). I have another file (file B)that
can
identify what the prefix means. This file B has the 3-digit prefix in
column A, the full name of the prefix in column B. How can I add a new
column in File A that has the full name of the prefix by creating a
formula
to look at File A, and if it contains a prefix that is on File B, it
will
add
the corresponding name to that row?