具有日期和文本的多条件查找 [英] Multi-condition lookup with dates and text

问题描述

我一直在想办法找出多条件查找所需的公式.

I have been melting my brain trying to work out the formula i need for a multiple conditional lookup.

我有两个数据集，一个是工作数据，另一个是合同数据.作业数据包含客户名称，作业位置和作业日期.我需要找出作业执行时是否签约，以及它是否从签约数据的N列中返回一个值.

I have two data sets, one is job data and the other is contract data. The job data contains customer name, location of job and date of job. I need to find out if the job was contracted when it took place, and if it was return a value from column N in the contract data.

当我尝试使用日期范围时会出现问题，因为每个客户经常有多个合同.

The problem comes when i try to use the date ranges, as there are frequently more than one contract per customer.

例如，在我的工作数据中:-

So for example, in my job data:-

客户|位置|工作日期

客户A |港口A |2014年1月1日

Cust A | Port A | 01/01/2014

客户A |港口B |2014年1月2日

Cust A | Port B | 01/02/2014

客户A在港口B拥有一份合同，该合同于2014年2月21日到期，因此在这里我希望它返回我的合同数据中N列的值，因为该工作处于合同中.客户A在工作时在A端口没有合同，所以我希望它返回无合同".

Customer A had a contract in port B that expired on 21st Feb 2014, so here i would want it to return the value from column N in my contract data as the job was under contract. Customer A did not have a contract in port A at the time of the job, so i would want it to return 'no contract'.

合同数据具有包含客户名称，端口名称，开始日期和结束日期值以及我的查找类别的列.

Contract data has columns containing customer name, port name, and a start and end date value, as well as my lookup category.

我认为我需要使用索引/匹配，但是我似乎无法让它们与我的日期范围一起使用.我可以使用另一种查询来使它正常工作吗?

I think i need to be using index / match but i can't seem to get them to work with my date ranges. Is there another type of lookup i can use to get this to work?

请帮助，我正在失去情节！

Please help, I'm losing the plot!

谢谢:)

推荐答案

您可以在此处使用两种方法:

You can use two approaches here:

1. 在结果表和源表中都创建了一个帮助程序列，该列连接了所有三个值，如下所示: = A2& B2& C2 .这样您会得到类似"Cust APort A01/01/2014"的信息.也就是说，您将获得一个唯一的值，通过该值您可以标识该行.您可以根据需要添加定界符: = A2&"|"& B2&"|"& C2 .然后，您可以通过此值执行 VLOOKUP .

1. In both result and source tables make a helper column that concatenates all three values like this: =A2&B2&C2. So that you get something like 'Cust APort A01/01/2014'. That is, you get a unique value by which you can identify the row. You can add delimiter if needed: =A2&"|"&B2&"|"&C2. Then you can perform VLOOKUP by this value.

您可以在源表中添加具有行号(1、2、3 ...)的帮助器列.然后您可以使用 = SUMIFS(< row_number_column>，< source_condition_column_1>，< condition_1>，< source_condition_column_2>，< condition_2>，...)返回源的行号符合所有三个条件的表格.您可以使用该行号执行 INDEX 或所需的任何操作.但是请小心:请检查源表中所有三列的唯一组合，否则这种方法可能会返回错误的结果.IE.如果在行 3 和 7 中满足匹配条件，则将返回 10 ，这是完全错误的.

You can add a helper column with row number (1, 2, 3 ...) in source table. Then you can use =SUMIFS(<row_number_column>,<source_condition_column_1>,<condition_1>,<source_condition_column_2>,<condition_2>,...) to return the row number of source table that matches all three conditions. You can use this row number to perform INDEX or whatever is needed. But BE CAREFUL: check that there are only unique combinations of all three columns in source table, otherwise this approach may return wrong results. I.e. if matching conditions are met in rows 3 and 7 it will return 10 which is completely wrong.

这篇关于具有日期和文本的多条件查找的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！