英文:
Is it appropriate to use vlookup in this scenario
问题
以下是翻译好的内容：
所以，我有一个看起来像这样的Excel表格：
姓名 类别 值
Joe A
Joe B
Joe C 4.2
Joe D 3.22
Brad A
Brad B
Brad C 2.25
Brad D 4.1
Ryan A
Ryan B
Ryan C 1.22
Ryan D 5.22
现在，我想让它看起来像这样：
姓名 类别 值
Joe A 4.2
Joe B 4.2
Joe C 4.2
Joe D 3.22
Brad A 2.25
Brad B 2.25
Brad C 2.25
Brad D 4.1
Ryan A 1.22
Ryan B 1.22
Ryan C 1.22
Ryan D 5.22
我考虑使用if语句结合vlookup（类似于=if(isblank([值单元格]),vlookup([姓名和类别单元格组合], [表中的所有列], 3, false),[值单元格])的方式，但vlookup似乎不接受多个条件。有没有办法让这里的语法工作，或者我应该使用不同的函数来完成任务？
英文:
So I have an Excel table that looks like this:
Name Category Value
Joe A
Joe B
Joe C 4.2
Joe D 3.22
Brad A
Brad B
Brad C 2.25
Brad D 4.1
Ryan A
Ryan B
Ryan C 1.22
Ryan D 5.22
Now, I want it to look like this:
Name Category Value
Joe A 4.2
Joe B 4.2
Joe C 4.2
Joe D 3.22
Brad A 2.25
Brad B 2.25
Brad C 2.25
Brad D 4.1
Ryan A 1.22
Ryan B 1.22
Ryan C 1.22
Ryan D 5.22
I was thinking of using an if statement combined with a vlookup (something like =if(isblank([value cell]),vlookup([name and category cell combination], [all columns in table], 3, false),[value cell]), but vlookup doesn't seem to accept multiple criteria. Is there a way to get the syntax to work here, or should I use a different function to do the job?
答案1
得分: 1
如果您使用Microsoft-365，可以尝试-
=BYROW(C2:C13,
LAMBDA(x,
LET(tmp,x:INDEX(C2:C13,12),
INDEX(FILTER(tmp,tmp<>""),1)
)))
英文:
If you user of Microsoft-365 then could try-
=BYROW(C2:C13,
LAMBDA(x,
LET(tmp,x:INDEX(C2:C13,12),
INDEX(FILTER(tmp,tmp<>""),1)
)))
答案2
得分: 1
对于Excel-2010，请尝试以下数组公式-
=INDEX($C$2:$C$13,MIN(IF($C2:$C$13<>"",ROW($C2:$C$13),""))-ROW($C$1))
数组公式意味着您必须按CTRL+SHIFT+ENTER输入该公式。
英文:
For Excel-2010 try the following array formula-
=INDEX($C$2:$C$13,MIN(IF($C2:$C$13<>"",ROW($C2:$C$13),""))-ROW($C$1))
Array formula means you must enter the formula by pressing CTRL+SHIFT+ENTER.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论