how to reference the other sheet in excel?

sheet1 have some data as the following:

      A          B         C             D
    1 number    name     unit         price

    2  1001      apple    box           20.00   

    3 1002        peach   box          10.00  

  ..................

now on the sheet2

      A          B         C             D
    1 number    name     unit         price

    2  1001            开发者_StackOverflow社区  

    3  

  ..................

now, if i type 1001 in A2 cell, i want the apple,box,20.00 can auto typed into the cells.thank you.

---

If your range in sheet1 is a1:d3, in sheet2 cell b2 you have to type

=CERCA.VERT(A2;Foglio1!$A$1:$D$3;2;FALSO)

in cell b3

=CERCA.VERT(A2;Foglio1!$A$1:$D$3;3;FALSO)

and in cell b4

=CERCA.VERT(A2;Foglio1!$A$1:$D$3;4;FALSO)

These are italian formulas.

I think that in english version it becomes something like this:

=VLOOKUP(A2,Sheet1!$A$1:$D$3,2,FALSE)

and so on for the other cells.

---

In cell B1

=If(IsError(Vlookup(A1, Sheet2!A:D, 2, false), "", Vlookup(A1, Sheet2!A:D, 2, false))

In Cell C1

=If(IsError(Vlookup(A1, Sheet2!A:D, 3, false), "", Vlookup(A1, Sheet2!A:D, 3, false))

In Cell D1

=If(IsError(Vlookup(A1, Sheet2!A:D, 4, false), "", Vlookup(A1, Sheet2!A:D, 4, false))