vlookup, excel

2009-11-06 1:17 am
vlookup, excel

If I have a table as follow,

| A | B | C |
1 | Item | size | price |
2 | skirt | S | $200 |
3 | jacket | L | $ 350 |
4 | shoes | M | $420 |
5 | jacket | S | $250 |
6 | skirt | M | $230 |
7 |
8 | item | size | Price
9 | jacket | M |

and I would like to find the price,

how should I solve this?

thanks

回答 (3)

2009-11-06 3:34 pm
✔ 最佳答案
查詢符合兩個條件或以上, 用 Sumproduct 較為合適

i.e.

=Sumproduct((A1:A10="Jacket")*(B1:B10="M")*C1:C10)

如把將要尋找 price 的 Item 及 Size 放 E2 及 F2

可在 G2 輸入:
=Sumproduct((A1:A10=E2)*(B1:B10=F2)*C1:C10)

please try
2009-11-06 6:23 pm
vlookup應難以用雙條件,
如 item & size的組合是不會有重複, sumproduct是最簡單及有效的方法
如 item & size的組合是有可能重複, 便不能用sumproduct
2009-11-06 3:53 am

圖片參考:http://i204.photobucket.com/albums/bb175/EddieOR/vlookup.png


1. 於儲存格 C9 輸入 =VLOOKUP("Jacket",A2:C6,3,FALSE)

("Jacket",A2:C6,3,FALSE):

Jacket = 於第一行要尋找的資料
A2:C6 = 要尋找的範圈
3 = 當尋找到所需的資料後,要向右的第三個儲存格的數據
FALSE = 數據需完全相同,TRUE 則為大約相同


收錄日期: 2021-04-27 13:12:29
原文連結 [永久失效]:
https://hk.answers.yahoo.com/question/index?qid=20091105000051KK00834

檢視 Wayback Machine 備份