Vlookup Excel，引用不同的工作表 [英] Vlookup Excel, Referencing A different Sheet

问题描述

我正在尝试使用以下方式为我的电子表格创建一个简单的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        Other

But 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，引用不同的工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！