Excel VLOOKUP #REF错误 [英] Excel VLOOKUP #REF Error

问题描述

我正在尝试创建一个综合的运动计划和训练日历.部分原因是一张纸可以根据用户在另一张纸上标识的比赛和日期来确定比赛发生的星期.我已经确认我的VLOOKUP没有引用不存在的列，并且该单元格的样式适用于常规样式.

I'm trying to create an integrated athletic planning and training calendar. Part of this is for one sheet to identify weeks in which races occur based upon races and dates identified by the user on another sheet. I've confirmed that my VLOOKUP is not referencing a non-existent column and that the cell style is General for those applicable.

这是公式:

=VLOOKUP(Periods!A6,Races!$F$2:$F$20,2,FALSE)

查找值"是第一张图片上的训练周"，用于标识目标工作表上的种族.该表上的每一行都有不同的培训周.我试图在公式表上获得D列(和F列)，以显示如果在适用的一周内计划进行一场比赛，则显示比赛的优先级.

The Lookup Value is the Training week on the first image and is used to identify races on the target worksheet. Each row on that sheet has a different training week. I am trying to get Column D (and F) on the formula sheet to show the priority of the race if there is one planned for the applicable week.

感谢您的帮助.

谢谢！

迈克

推荐答案

此处的vlookup中的，2 = VLOOKUP(Periods！A6，Races！$ F $ 2:$ F$ 20,2，FALSE)的意思是，如果在F列中找到匹配项，请转到F2:F20列的右侧，但是，您需要指定F2:G20才能在vlookup公式中使用.(仍然无法获得预期的结果)

the ,2, in your vlookup here =VLOOKUP(Periods!A6,Races!$F$2:$F$20,2,FALSE) means go to the right of column F2:F20 if a match is found in column F, but, you need to specify F2:G20 for that to work in your vlookup formula. (which still won't give you the results you are expecting)

您有两个选择

选项1:重新组织目标工作表，以便您的查找值在左侧，因此您可以使用vlookup在其右侧拉出列

Option 1: reorganize your target worksheet so your lookup value is on the left, so you can use vlookup to pull columns to the right of it

选项2，使用另一种公式，例如匹配+索引"组合.(未经测试，但下面是示例)

Option 2, use a different kind of formula like a Match + index combination. (untested, but example below)

=Index(Races!$B$2:$B$20,match(Periods!A6,Races!$F$2:$F$20,0))

这篇关于Excel VLOOKUP #REF错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！