使用索引匹配从多列中使用查找值返回值 [英] Use Index Match to Return Value Using Lookup Value from Multiple Columns

问题描述

我正在尝试使用3列范围内的值从一列返回值.当我在报告(D14)中输入电气，管道或气体许可证号时，我希望将相应的建筑物许可证号输入到另一个单元格中作为文件参考.该报告是一个工作表，许可编号列表是同一工作簿中的另一个工作表.这是一份许可的报告，所以我认为我不需要任何复杂的事情. VLOOKUP无法正常工作，因为我需要从右到左搜索.

I'm trying to return a value from one column using values from a range of 3 columns. When I enter an electrical, plumbing or gas permit number into a report (D14) I want the corresponding building permit number to be entered into another cell as the file reference. The report is one worksheet and the list of permit numbers is another worksheet in the same workbook. It's one permit one report so I don't think I need anything complicated. VLOOKUP didn't work because I need to search right to left.

这是我使用的索引匹配"公式，但我只能使用1列作为查找值，并且每种许可类型都有一列.

This is the Index Match formula I've used but I can only use 1 column as the lookup value and I have a column for each type of permit.

=INDEX(Building!$A2:$A25000,MATCH(D14,Building!$D2:$D25000,0))

推荐答案

包装您的

Wrap your MATCH function(s) in IFERROR function(s) so that the lookup is passed to the next column if it is not found.

=INDEX(Building!$A:$A, IFERROR(MATCH(D14, Building!$D:$D, 0),
                       IFERROR(MATCH(D14, Building!$E:$E, 0),
                       IFERROR(MATCH(D14, Building!$F:$F, 0), "nowhere"))))

这篇关于使用索引匹配从多列中使用查找值返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！