Hi,

I have vendors information on sheet #1. Each vendor has its numerical ID.
Info such as address , city, State, Tel, Fax are in the same row in separate
cells. On sheet # 2, I want to create a form that will require all of the
above info in differnt cells. Is there a way that on my form, if i enter the
ID#, the othe cells will get address, tel, fax etc. from sheet #1.

Hi

VLOOKUP(ID,TableName or range,Offset column,O) Zero is the type and will
return #N/A! value if ID is not in in the table.

Look in help for more details.

Peter

"MIK" wrote:

Hi,

I have vendors information on sheet #1. Each vendor has its numerical ID.
Info such as address , city, State, Tel, Fax are in the same row in separate
cells. On sheet # 2, I want to create a form that will require all of the
above info in differnt cells. Is there a way that on my form, if i enter the
ID#, the othe cells will get address, tel, fax etc. from sheet #1.

Hi,

You want to use the VLOOKUP functions such as

=VLOOKUP(A1,Sheet2!A$1:F$100,2,FALSE)

In this example you are looking up the ID in cell A1 in column A of Sheet2
and your are returning the entry in column 2 of that table from the row with
a matching ID.

You can simplify this to

=VLOOKUP(A1,Sheet2!A$1:F$100,2,0)
but not
=VLOOKUP(A1,Sheet2!A$1:F$100,2,O)

(the last argument is zero not the letter "O".

You can simplify this further to

=VLOOKUP(A1,Sheet2!A$1:F$100,2)

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"MIK" wrote:

Hi,

I have vendors information on sheet #1. Each vendor has its numerical ID.
Info such as address , city, State, Tel, Fax are in the same row in separate
cells. On sheet # 2, I want to create a form that will require all of the
above info in differnt cells. Is there a way that on my form, if i enter the
ID#, the othe cells will get address, tel, fax etc. from sheet #1.

You can simplify this further to
=VLOOKUP(A1,Sheet2!A$1:F$100,2)

Only if the table_array is sorted in ascending order by column A.


--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
Hi,

You want to use the VLOOKUP functions such as

=VLOOKUP(A1,Sheet2!A$1:F$100,2,FALSE)

In this example you are looking up the ID in cell A1 in column A of Sheet2
and your are returning the entry in column 2 of that table from the row
with
a matching ID.

You can simplify this to

=VLOOKUP(A1,Sheet2!A$1:F$100,2,0)
but not
=VLOOKUP(A1,Sheet2!A$1:F$100,2,O)

(the last argument is zero not the letter "O".

You can simplify this further to

=VLOOKUP(A1,Sheet2!A$1:F$100,2)

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"MIK" wrote:

Hi,

I have vendors information on sheet #1. Each vendor has its numerical ID.
Info such as address , city, State, Tel, Fax are in the same row in
separate
cells. On sheet # 2, I want to create a form that will require all of the
above info in differnt cells. Is there a way that on my form, if i enter
the
ID#, the othe cells will get address, tel, fax etc. from sheet #1.