根据其他单元格中的值设置单元格值 [英] Set cell value based on value in other cell
问题描述
我有一个下表结构:
--- c1 ------- c2 ---- c3 ------ c4
r1:** 0001 ** ** word1 **
r2:0002 word2 ** 0001 ** ** word1 **
r3:0003 word3 0002 word2
我想自动设置列三个值,以便如果列中有一些单词4,那么程序应该检查列2,并尝试找到相同的单词。如果找到,那么列1的值应该被用作列3的值。
我可以手动执行,但是更多的工作,如果我添加新的行。列1值等于行号,因此如果添加了新行,则它将自动更新。现在我想设置我的数据,以便在这些情况下,第3列会自动更新。
发生这种情况的正确公式是什么?
您想要的公式是:
= IF(D1,INDEX(A:A,MATCH(D1,B:B,0)),)
<
= IF([@ Col4]",INDEX([Col1],MATCH([@ Col4],[Col2],0)),)
I have a following table structure:
--- c1 ------- c2 ---- c3 ------ c4
r1: **0001** **word1**
r2: 0002 word2 **0001** **word1**
r3: 0003 word3 0002 word2
I would like to set column three values automatically, so that if there is some word in column 4, then program should check column 2 and try to find the same word. If found, then column 1 value should be used as a column 3 value.
I could do that manually, but it's more work and it gets problematic if I ever add new rows. Column 1 value is equal to row number, so it's updated automatically if new rows added. Now I would like to set my data so that also column 3 would be updated automatically in those cases.
What is the right formula to make that happen?
The formula you want is:
=IF(D1<>"",INDEX(A:A,MATCH(D1,B:B,0)),"")
EDIT
For structured table references:
=IF([@Col4]<>"",INDEX([Col1],MATCH([@Col4],[Col2],0)),"")
这篇关于根据其他单元格中的值设置单元格值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!