excell lookup function

以下是題目資料的連結因為不可以貼圖所以直接貼網頁。

請下載turtoral 7,下載後打開裡面CASE 1就是我要問的問題。

http://oc.course.com/np/Office2007/Excel.cfm

請問如果要在Purchase Order Work sheet輸入lookup函數去找product list worksheet裡面的資料像是product type,model name,和price要怎樣找呢?

以下是題目原文:

In the Purchase Order worksheet,Product ID numbers will be entered in cell B5.Create a lookup function to display the product type in cell C7,the model name in cell C8,and the price in cell C9.Product information is displayed in the Produce List worksheet.

1 Answer

Rating
  • ?
    Lv 7
    1 decade ago
    Favorite Answer

    使用簡單的 VLOOKUP 函數:

    C7=VLOOKUP($B$5,'Product List'!$A$7:$D$119,2,0)

    C8=VLOOKUP($B$5,'Product List'!$A$7:$D$119,3,0)

    C9=VLOOKUP($B$5,'Product List'!$A$7:$D$119,2,0)

    若要再進一步做, 可使用 [插入]-[名稱]-[定義], 先設定 2 個名稱:

    [ID] 參照到 [=OFFSET('Product List'!$A$8,,,COUNTA('Product List'!$A:$A)-1)]

    [DATA] 參照到 [=OFFSET('Product List'!$A$8,,,COUNTA('Product List'!$A:$A)-1,4)]

    在B5儲存格設定按 [資料]-[驗証], 下拉到 [清單], 來源輸入 [=ID]

    在C7輸入:

    =IF($B$5="","",VLOOKUP($B$5,DATA,ROW()-5,0))

    然後下拉複製公式至C9即完成, 如附件:

    http://www.funp.net/812286

Still have questions? Get your answers by asking now.