Excel - 搜索缺失值并根据3列返回这些值 - MATCH / INDEX / IF组合？ [英] Excel - search for missing values and return these values based on 3 columns - MATCH/INDEX/IF combo?

问题描述

这对我来说有点太专家了，希望你们中的一个可以帮助我！

This gets a bit too expert for me, hopefully one of you can help me!

查看图片我想要实现的目标。红色文字显示了我想要的内容。 List3 包含 List1 的所有可能值。 List2 显示 List1 的当前有效值，我想根据 List3 返回缺失值。

See the image for what I am trying to achieve. The red text shows what I want. List3 contains all the possible values for List1. List2 displays the currently active values for List1, and I want to return the missing values based on List3.

我猜我在看MATCH / INDEX / IF函数但我无法想出这个。有谁愿意帮我一个忙吗？ < 3非常感谢！

I'm guessing I'm looking at a MATCH/INDEX/IF function but I cannot figure this one out. Anyone out there willing to give me a hand? <3 Thanks a bunch!

推荐答案

将此公式放入F4并填写。

Put this formula in F4 and fill down.

=INDEX(C:C, AGGREGATE(15, 7, ROW(C$2:C$11)/NOT(COUNTIFS(B:B, C$2:C$11, A:A, E4)), COUNTIF(E$4:E4, E4)))

当然，这个公式取决于E4中的'数字'列表：E7是准确的并且足够用于捕获所有缺失的条目。

Of course, this formula is dependent on the list of 'numbers' in E4:E7 being accurate and sufficiently populated to catch all missing entries.

这篇关于Excel - 搜索缺失值并根据3列返回这些值 - MATCH / INDEX / IF组合？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！