Excel - 一个单元格中的多个IF [英] Excel - Mutiple IFs in one cell

问题描述

好吧，标题是一个头发的误导。首先，我在Google电子表格中。第二，这不仅仅是一个IF（条件，返回IF（...））的情况（另外，我正在环顾四周，看看我甚至可以在这个网站上发布一个excel公式问题...没有看到太多所以如果这不是适当的地方自由地这样说，我会删除）。

我有一个表，我需要从一个基于2单元格：

  -3 -2 -1 0 1 2 3 4 5 
 ___________________________ 
 A | 0，0，0，1，2，4，8，12,16 
 B | 0，0，1，2，4，8，12,16,20 
 C | 0，1，2，4，8，12,16,20,24 
 D | 1，2，4，8，12,16,20,24,28 
  

我基本上需要C1如果A1是A，B，C或D，则B1将在-3 - 5的范围内返回。

我的所有尝试都是#error，所以在最后一个小时我没有什么可以显示出来。我相信有一种方式。如果你想让我失败的尝试让我知道。

解决方案

假设你的表存储在 Sheet2 ，那么您可以在单元格 C1 中使用以下公式：

  = IFERROR（VLOOKUP（$ A $ 1，Sheet2！$ A $ 1：$ J $ 5，$ B $ 1 + 5，FALSE），0）
  

VLOOKUP 函数查找所需字母的行（如 A1 ）。如果找到，它将返回由 B1 的内容指示的列中的值，并添加5以转置为正确的值。 FALSE 参数表示查找应该完全匹配。

如果没有找到该值， a href =http://office.microsoft.com/en-us/excel-help/iferror-function-HA010342587.aspx?CTT=1 =nofollow> IFERROR 包裹在 VLOOKUP 将负责插入 0 。

我分享了 Google电子表格示例，公式在 Sheet1 和 Sheet2 上的表。

Alright, the title is a hair misleading. First, I'm in Google Spreadsheet. Second, This more than a IF(condition, return IF(...)) kind of situation (Also, I was looking around to see if I could even post an excel formula question on this site... didn't see much so if this is not the proper place feel free to say so and I'll delete).

I have a table that I need to get a value from based on 2 cells:

   -3 -2 -1 0  1  2  3  4  5
  ___________________________
A| 0, 0, 0, 1, 2, 4, 8, 12,16
B| 0, 0, 1, 2, 4, 8, 12,16,20
C| 0, 1, 2, 4, 8, 12,16,20,24
D| 1, 2, 4, 8, 12,16,20,24,28

I basically need C1 to equal what would be returned if A1 was "A", "B", "C", or "D" , and B1 was in the range of -3 - 5.

All of my attempts have been #error, so I don't have anything to show for the last hour I've put into figuring this out. I'm confident there's a way though. If you want my failed attempts let me know.

解决方案

Suppose that your table is stored on the top left of Sheet2, then you can use the following formula in cell C1:

=IFERROR(VLOOKUP($A$1,Sheet2!$A$1:$J$5,$B$1+5,FALSE),0)

The VLOOKUP function looks up the row where your desired letter is (as given in A1). If it is found, it will return the value in the column indicated by the contents of B1 with 5 added to transpose to the right value. The FALSE parameter indicates that the lookup should do an exact match.

If the value is not found, the IFERROR wrapped around VLOOKUP will take care of inserting the 0.

I shared a Google Spreadsheet example, with the formulas on Sheet1 and the table on Sheet2.

这篇关于Excel - 一个单元格中的多个IF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！