Excel 2000 - formula to display text from anoher worksheet

[Scott J]

I have 2 worksheets. On sheet 1 is a list of items on order sorted by
customer number. The other sheet is a list of customer numbers in column A
and coresponding names in column B. I want to display the customer name
instead of the customer number in sheet 1 from the coresponding name in
column B that matches the number in column A in sheet 2. What formula can I
use for this?

 

[Ron Coderre]

With
Sheet1!A1: (a CustNum)
and
Sheet2!A1:B100 containing the XRef list of CustNum/Name

Try this on Sheet1:
This formula returns the name associated with the CustNum
B1: =VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0)

Note: if an incorrect CustNum is in Col_A,
the formula returns an error.

If you need error checking,
try this formula (in sections for readability):
B1:=IF(ISNUMBER(MATCH(A1,Sheet2!$A$1:$A$100,0)),
VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0),"No match")

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Scott J]

The first formula works but I didn't realize I have first and last names.
Column B on sheet 2 contains the last names and column C on sheet 2 contains
first names. How can I make it so another column in sheet 1 does the same
thing with the first names from column C on sheet 2? I tried tweaking it but
am having a hard time figuring it out.
Thanks

 

[Ron Coderre]

Try this:

Last Names:
B1: =VLOOKUP(A1,Sheet2!$A$1:$C$100,2,0)

First Names:
C1: =VLOOKUP(A1,Sheet2!$A$1:$C$100,3,0)

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Scott J]

Thank You Ron, That did it. I appreciate your time.
Scott