我正在尝试使用以下方式为我的电子表格创建一个简单的VLOOKUP函数:在第一页
= VLOOKUP(工资,2015年12月,$ D $ 4:$ E $ 43,1,FALSE)这是我试图参考的表:
我正在尝试参考的表格:
价值类别 ======= ========== £530.00 Charlotte Owing - £53.00 Gym - £16.47水 - £67.00电话 - £11.01礼物 - £14.40吃出 - £100.00食物 - £65.00其他但是当我将VLOOKUP代码放入我的excel中时,它返回NA。谁能看到导致错误的原因?
解决方案
E4:F4中的公式是
= INDEX('December 2015_natwest_download'!A:A,MATCH(D4,'December 2015_natwest_download'!B:B,0)) = SUMIFS('December 2015_natwest_download'!A:A,'December 2015_natwest_download'! B:B,D4)请注意,F5中的SUMIFS返回两个条目。
I am trying to create a simple VLOOKUP function for my spreadsheet using the below: In the first sheet
=VLOOKUP("Salary",'December 2015_natwest_download'!$D$4:$E$43,1,FALSE)This is the sheet i am trying to reference:
The sheet I am trying reference:
Value Category ======= ========== £530.00 Charlotte Owing -£53.00 Gym -£16.47 Water -£67.00 Phone -£11.01 Presents -£14.40 Eating out -£100.00 Food -£65.00 OtherBut when I put the VLOOKUP code into my excel, it returns NA. Can anyone see what is causing the error?
解决方案The VLOOKUP function is designed to lookup a value on the far left of a block of data and return a corresponding value from a column to the right.
If you need to lookup a value and return a value from a corresponding column to the left of the lookup column, you need to use an INDEX/MATCH function pair.
If you are returning numbers based on a condition (either in that column or another column) either the SUMIF or SUMIFS function will do. Individual entries can be easily collected but if there is more than a single match to your condition, you will receive a sum total of the matching numbers.
The formulas in E4:F4 are,
=INDEX('December 2015_natwest_download'!A:A, MATCH(D4, 'December 2015_natwest_download'!B:B, 0)) =SUMIFS('December 2015_natwest_download'!A:A,'December 2015_natwest_download'!B:B, D4)Note that the SUMIFS in F5 is returning two Gym entries.
更多推荐
Vlookup Excel,引用不同的工作表
发布评论