Excel匹配，偏移，多列索引 [英] Excel Match, Offset, Index for multiple columns

问题描述

我想查找相应客户的值PO#发票#报价#等.&客户端列表在不同的多个列中都有提及.

I want to find the value of corresponding clients PO # Invoice # Quote # etc.. & client list is mentioned in different multiple columns.

使用的公式为:= INDEX(D16，D17,2，MATCH('客户PO和发票明细'！A1:ACZ25，'客户PO和发票明细'！A1:ACZ1,0))

Formula used : =INDEX(D16,D17,2,MATCH('Client PO & Invoice Details'!A1:ACZ25,'Client PO & Invoice Details'!A1:ACZ1,0))

使用匹配偏移量索引查找所需的查找功能

LOOKING FOR REQUIRED LOOKUP FUNCTIONS WITH MATCH OFFSET INDEX

推荐答案

您的数据暂存确实效率很低.如果您创建了一个客户编号列，并且垂直而不是水平扩展了数据，那么您真的可以省去很多麻烦.例如，如果以这种方式组织数据，则可以使用数据透视表来大大简化此问题.

The staging of your data is really inefficient. If you made a client number column and expanded the data vertically rather than horizontally, you could really save yourself a lot of headaches. For example, if your data was organized that way you could use a pivot tabel to massively simplify this problem.

除非对数据结构进行更改，否则可以针对偏移范围对日期进行vlookup:

Barring changes to your data structure, you can do a vlookup for the date against an offset range:

=VLOOKUP($D$17,OFFSET('Client PO & Invoice Details'!$A$1,0,MATCH($D$16,'Client PO & Invoice Details'!$1:$1,0)-1,1048575,6),2,FALSE)

此公式应放在ID和日期过滤器下方的D18:D22中.您需要将vlookup索引增加到公式的末尾(...,2,FALSE)来获取您要查找的特定列.在后续公式中2必须更新为3、4、5和6以获得每个值.

This formula should be placed in D18:D22 below the id and date filters. you will need to increment the vlookup index toward the end of the formula ( ...,2,FALSE) to grab the specific column you are looking for. The 2 will need to be updated to 3,4,5, and 6 in the subsequent formulas to get each of the values.

首先，该公式在第1行中找到您的客户编号，然后该偏移量从找到该客户编号的列开始定义一个6列宽的范围，最后vlookup照常工作，方法是在该客户的第一列中找到日期偏移范围并返回索引列的值.

First The formula finds your client number in row 1, next the offset defines a range 6 columns wide starting at the column that the client number was found in, lastly the vlookup works as usual by finding the date in the first column of the offset range and returning the value of the indexed column.

这篇关于Excel匹配，偏移，多列索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！