I have sheet 2 with 496 rows of information, each row contains a row for
material number(cells A1:A496), description(cells B1:B496), and a UOM(cells
C1:C496). I want to be able to type in the material number in cell A1 on
sheet1 and populate cell B1 and C1 with the correct information that relates
to that material number.


Sheet2
MATERIAL DESCRIPTION UOM
200209 Closure PP Unlined SC Red 28mm 6.2g TH
200210 CAPS GEN 24OZ PET SPT CAP BLUE TH
200226 WRAP OZ 8OZ 8PK FILM EA
200243 Film LDPE ICMT Spr 6x0.5L 11.25in 2mil EA
200258 Film LDPE ZPHRL Spr 12x0.5L 30in 2.5mil EA
200259 HANDLE GEN EA


Sheet1

A1 B1
C1

Material Number(I will type this information) Description
UOM

202209 Closure PP Unlined SC Red 28mm 6.2g
TH
(THESE TWO CELLS SHOULD POPOLATE ON THEIR OWN)

B1: =VLOOKUP(A1,Sheet2!$A$1:$C$496,2,0) will return Description


C1: =VLOOKUP(A1,Sheet2!$A$1:$C$496,3,0) will return UOM

If errors could occur:

=IF(ISNA(VLOOKUP(A1,Sheet2!$A$1:$C$496,2,0)),"",VL OOKUP(A1,Sheet2!$A$1:$C$496,2,0))

will return blank if no match on material number

HTH

"BAKERSMAN" wrote:

I have sheet 2 with 496 rows of information, each row contains a row for
material number(cells A1:A496), description(cells B1:B496), and a UOM(cells
C1:C496). I want to be able to type in the material number in cell A1 on
sheet1 and populate cell B1 and C1 with the correct information that relates
to that material number.


Sheet2
MATERIAL DESCRIPTION UOM
200209 Closure PP Unlined SC Red 28mm 6.2g TH
200210 CAPS GEN 24OZ PET SPT CAP BLUE TH
200226 WRAP OZ 8OZ 8PK FILM EA
200243 Film LDPE ICMT Spr 6x0.5L 11.25in 2mil EA
200258 Film LDPE ZPHRL Spr 12x0.5L 30in 2.5mil EA
200259 HANDLE GEN EA


Sheet1

A1 B1
C1

Material Number(I will type this information) Description
UOM

202209 Closure PP Unlined SC Red 28mm 6.2g
TH
(THESE TWO CELLS SHOULD POPOLATE ON THEIR OWN)

Another way. Select cells B1 & C1 and enter this formula while both cells
are selected. The Hit Ctrl+Shift+Enter. You will get these brackets around
the formula...{ }.

=IF(ISNA(VLOOKUP(A1,F1:H4,{2,3},0)),"",VLOOKUP(A1, F1:H4,{2,3},0))

If you need to change the formula sometime along the way during use you MUST
select both B1 & C1 to make changes and the again use C+S+E to commit the
formula.

If your data to return expands you can just expand the columns to return.
In this case you would select B1 through F1 and enter this formula same as
above.

=IF(ISNA(VLOOKUP(A1,F1:H4,{2,3,4,5,6},0)),"",VLOOK UP(A1,F1:H4,{2,3,4,5,6},0))

HTH
Regards,
Howard

"BAKERSMAN" wrote in message
...
I have sheet 2 with 496 rows of information, each row contains a row for
material number(cells A1:A496), description(cells B1:B496), and a
UOM(cells
C1:C496). I want to be able to type in the material number in cell A1 on
sheet1 and populate cell B1 and C1 with the correct information that
relates
to that material number.


Sheet2
MATERIAL DESCRIPTION UOM
200209 Closure PP Unlined SC Red 28mm 6.2g TH
200210 CAPS GEN 24OZ PET SPT CAP BLUE TH
200226 WRAP OZ 8OZ 8PK FILM EA
200243 Film LDPE ICMT Spr 6x0.5L 11.25in 2mil EA
200258 Film LDPE ZPHRL Spr 12x0.5L 30in 2.5mil EA
200259 HANDLE GEN EA


Sheet1

A1 B1
C1

Material Number(I will type this information) Description
UOM

202209 Closure PP Unlined SC Red 28mm 6.2g
TH
(THESE TWO CELLS SHOULD POPOLATE ON THEIR
OWN)