提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

如何在Excel中从右向左查看值?

Vlookup在Excel中是一个有用的函数,我们可以用它来快速返回表中最左边一列的相应数据。 但是,如果要在其他列中查找特定值并将相对值返回到左侧,那么普通的查找函数将不起作用。 在这里,我可以介绍其他的公式来解决这个问题。

使用VLOOKUP和IF函数从右向左查找值

使用INDEX和MATCH函数从右向左查找值


从左到右查找一个值:

用这个公式 Kutools for Excel,您可以快速查看没有任何公式的列表中的确切值。

DOC-VLOOKUP函数,6

Kutools for Excel:比200方便的Excel加载项,可以在60天免费试用。 下载并免费试用现在!


箭头蓝色右泡 使用VLOOKUP和IF函数从右向左查找值


要从正确的特定数据中获得相应的左值,以下的vlookup函数可以帮助您。

假设你有一个数据范围,现在你知道这个人的年龄,你想要得到他们的相对名称在左边的名称列中,如下面的截图所示:

DOC-VLOOKUP到左,1

请输入此公式到您需要的单元格中: =VLOOKUP(F2,IF({1,0},$D$2:$D$10,$B$2:$B$10),2,0) 并按下 输入 键,你将得到你需要的正确结果,看截图:

DOC-VLOOKUP到左,1

然后将填充柄拖动到要应用此公式的单元格以获取特定年龄的所有对应名称。

DOC-VLOOKUP到左,1

备注:

1。 在上面的公式中, F2 是您想要返回其相关信息的价值, D2:D10 是您正在查找的列 B2:B10 是包含您希望返回的值的列表。

2。 向下拖动此公式时,绝对引用$ D $ 2:$ D $ 10和$ B $ 2:$ B $ 10保持不变,而相对引用F2更改为F3,F4,F5 ...。


箭头蓝色右泡 使用INDEX和MATCH函数从右向左查找值

除了上面的公式,这里是另一个与INDEX和MATCH混合的公式,也可以帮你一个忙。

键入这个公式: =INDEX($B$2:$B$10,MATCH(F2,$D$2:$D$10,0)) 并按下 输入 键来获得你需要的相应数据,看截图:

DOC-VLOOKUP到左,1

然后将填充手柄向下拖到您想要包含此公式的单元格中。

注意:在这个公式中, F2 是您想要返回其相关信息的价值, B2:B10 是包含您想要返回的值的列表 D2:D10 是你正在寻找的列。


相关文章:

如何在Excel中使用vlookup确切和近似匹配?

如何在Excel中查找值匹配区分大小写?

如何在Excel中查找获取行号?


Kutools for Excel - 最佳办公生产力工具提高80%的生产力

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能60天免费试用。
kte tab 201905

Office选项卡为Office提供选项卡式界面,使您的工作更轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Tomas · 2 days ago
    Hi, I am looking for a way for excel to pull the right most number in a table that gets update every day. Please advise.
  • To post as a guest, your comment is unpublished.
    Kyle · 9 months ago
    Here’s a pretty great explanation:
    https://youtu.be/ceBLc-tBj5g
  • To post as a guest, your comment is unpublished.
    Deepak Singh · 9 months ago
    please ans : if on sheet1 one a1=Salesman_ name and a2 to a5 city name where all sales man Sales report how much Sales done . and sheet2 2 all sales man Report like which salesMan done how much sales( useing Countifs formula) by city wise and Total Sales ,i need a formula where when i use hyperlink on city wise sales and total slaes when i click total any one slaes man then get filter data on sheet1 of particuler sales man.again go back sheet2 and click anohter sales man then again get another slaes mann filered data . pleaes Help me.
  • To post as a guest, your comment is unpublished.
    Deepak Singh · 9 months ago
    hay any body can ans me.
    in A1 cell time is 23:59:00 and A2 cell 00:00:00 how can we get time between both date its always get show error .its meance 23:59:00 on date 18/11/2018 and 00:00:00 is next date 19/11/2018 so how can get betweeen time
  • To post as a guest, your comment is unpublished.
    asr · 1 years ago
    Thanks.....its works
  • To post as a guest, your comment is unpublished.
    Sarah Tanner · 1 years ago
    Hi!

    I'm trying to show a cell adjacent to a referenced cell when the referenced cell could be in one of two columns.

    The referenced cell, M9, uses this function to find the upcoming date closest to today (i.e. which bill is due next):

    =INDEX($K$1:$K$160,MATCH(M9,$L$1:$L$160,0))


    I want to cell M8 to show the AMOUNT due on that day, which is in the cell to the LEFT of the referenced cell in the list.

    I figured out in O9 how to show it when M9 references a cell in a single column L:

    =INDEX($K$1:$K$160,MATCH(M9,$L$1:$L$160,0))


    But I can't figure out how to have that apply when the referenced cell is in column N.


    A few things I've tried in O10-O12 that didn't work:
    =INDEX($K$1:$K$160&$M$1:$M$160,MATCH(M9,$L$1:$L$160&$N$1:$N$160,0))
    =INDEX(K1:K160,MATCH(M9,L1:L160,0))OR(M1:M160,MATCH(M9,N1:N160,0))
    =INDEX(K1:M160,MATCH(M9,L1:N160,0))

    Would love some help! Thanks!
  • To post as a guest, your comment is unpublished.
    Jajoo · 2 years ago
    very confusing, is there any youtube video about this?
  • To post as a guest, your comment is unpublished.
    Md. Nazmul Hoque · 2 years ago
    Thank you very much...
  • To post as a guest, your comment is unpublished.
    Manmohan · 2 years ago
    Thank u thank u so much
  • To post as a guest, your comment is unpublished.
    Gajraj singh · 2 years ago
    please make me understand "IF({1,0},$D$2: $D$10,$B$2:$B$1 0)", how does it works.
  • To post as a guest, your comment is unpublished.
    guillaume · 3 years ago
    you better use the choose 1,2 fct : example :
    =VLOOKUP(A6,CHOOSE({1,2},'TP Input'!$Z$3:$Z$36,'TP Input'!$Y$3:$Y$36),2,0)
  • To post as a guest, your comment is unpublished.
    summer · 3 years ago
    If you found this too confusing, like me, here's an alternative. Create a column on the right of the Age column. Copy and paste all data from Name. So now you have 2 columns with the same data: Name, Age, Name. Now you can do a Vlookup for the name by using the age.

    Then before you submit it to your boss, delete the extra name column to avoid confusion.

    :)
  • To post as a guest, your comment is unpublished.
    Milind · 4 years ago
    Can you Specify "IF({1,0},$D$2:$D$10,$B$2:$B$10)" from - =VLOOKUP(F2,IF({1,0},$D$2:$D$10,$B$2:$B$10),2,0)