How to fill out a table (a row) with data from another worksheet

[braveheart]

I have two Excel worksheets:

Worksheet #1. - it has extensive information on customers, a fragment
of it is shown below:

Cust # Customer Name Country Region
EL017 A&A Comercial S.A. Ecuador CA
JA031 Kichi Tol Co Japan AP
IT014 A. Nardu, S.R.L. Italy EU

Worksheet #2. - is to be filled out:

INV# CUST# CUSTOMER NAME COUNTRY REGION
75836A JA031 Kichi Tol Co Japan AP
75870AX CR012 Invot S.A. Costa Rica CA

INV# - is a column with Invoice numbers to be filled out from a paper
file

CUST# , CUSTOMER NAME, COUNTRY, REGION - are columns to be filled out
automatically based on the information in worksheet #1.

How can I relate 2 sheets so that when I put into worksheet#2
customer's number (from worksheet#1) cells next to it will be filled
out with "customer name" , "country", "region information",
etc. ??

I created a dropdown menu in worksheet #1 in customer# so I can chose
the customer number but when I tried to create an IF formula relating
the data in the two sheet to fill out the row with corresponding data,
it didn't work..

Thank you soo much for you help!!!

 

[Guest]

Braveheart --

What you want to do is use a LOOKUP table.

Task A -- Name the range you want to draw info from. This will make it a
lot easier later on.

Step 1 -- Highlight the table from which you'll draw info.
Step 2 -- >Insert>Name>Create and then enter the name. (perhaps "Data")

Task B -- Set up the lookup formula as follows:

A B C
1 Inv # Cust # =VLOOKUP(B1,Data,2)

that should return the name of the company.

HTH

 

[Dave Peterson]

Since the OP will be matching on an Invoice number, I would expect that they
want an exact match.

I'd use that 4th argument:

=VLOOKUP(B1,Data,2,False)

Debra Dalgleish has some notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())

 

[braveheart]

Thak you very much!

Braveheart --

What you want to do is use a LOOKUP table.

Task A -- Name the range you want to draw info from. This will make it a
lot easier later on.

Step 1 -- Highlight the table from which you'll draw info.
Step 2 -- >Insert>Name>Create and then enter the name. (perhaps "Data")

Task B -- Set up the lookup formula as follows:

A B C
1 Inv # Cust # =VLOOKUP(B1,Data,2)

that should return the name of the company.

HTH

 

[braveheart]

Thanks a lot!

Since the OP will be matching on an Invoice number, I would expect that they
want an exact match.

I'd use that 4th argument:

=VLOOKUP(B1,Data,2,False)

Debra Dalgleish has some notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())