需要找到与两列条件匹配的值.可能的 VLOOKUP [英] Need to find a value that matches two columns of criteria. Possible VLOOKUP

问题描述

下方更新 -- 6/4

我在 excel 中有两个工作表.一个是带有支票#/amount/Donor ID(donations"工作表)的捐赠者列表，另一个是带有Donor ID/check#/amounts(quickbooks 工作表).Quickbooks 尚未填写 DonorID.

我遇到的问题是我需要将捐赠者 ID 与他们的支票相匹配.为了得到这个，我需要将Quickbooks"中的 check# 和 amount 与Donations"中的相同，当它们匹配时，它会给我捐赠者 ID对应那个检查.

这是它的布局方式:

捐赠工作表:乙丙捐赠ID支票#金额1 179 106 200 美元2 210 106 500 美元3 220 106 600 美元Quickbooks 工作表:乙丙捐赠ID支票#金额1 不适用 106 2002 不适用 1074 5003 不适用 300 1000

当我要求查找check# 106 is for $200"时，它应该告诉我它来自 Donor 179.

有些支票不匹配，也不是来自捐赠者.该列表有近 50000 个名字.

如果有任何问题，请问我，以便我能更清楚地说明这一点.我对这一切也有些陌生，如果我不明白，请道歉.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

感谢大家的帮助.我们还没有到那里，但你们都在引导我朝着正确的方向前进.

我添加了页面的屏幕截图以供参考，因为团队在使给定的公式起作用时遇到了问题.我还将两张工作表合并到一页上，这样可以减少交叉工作表的引用，并且更易于阅读.

Cknum = Check#

解决方案

两列匹配通常采用两种典型配置之一；一个是数组公式，另一个是标准公式.两者都使用 到 已用于捕获不匹配并显示 不匹配 而不是 #N/A 错误.

我已经修改了您的单个工作表示例数据中的值，以显示各种匹配项以及 chknum 和 amount 被两个不同的复制em>donationID 条目.

匹配的记录采用颜色编码以供快速参考.我已在我的 Docs.com-Preview 站点上公开发布了此示例工作簿.

VLOOKUP_w_Multiple_Criteria_and_Duplicates.xlsx

Update Below -- 6/4

I have two worksheets in excel. One is a list of donors with their check#/amount/ Donor ID ( "donations" worksheet) and the other is a copy of the accounting info with Donor ID/check#/amounts (quickbooks worksheet). Quickbooks does not have the DonorID's filled in yet.

The issue I have is that i need match up Donor ID's with their checks. To get this i need to match the check# and amount in "Quickbooks" to the same in "Donations", when they match, it will give me the Donor ID that corresponds with that check.

Here is how it is laid out:

Donations Worksheet:
     A                  B           C
 DonationID           Check#     Amount
1   179                106        $200
2   210                106        $500
3   220                106        $600

Quickbooks Worksheet:
      A                B          C
  DonationID         Check#     Amount
1    n/a              106        200
2    N/a              1074       500
3    N/a              300        1000

When I ask to find "check# 106 is for $200" it should tell me that it is from Donor 179.

Some checks don't match and are not from donors. The list has close to 50000 names.

Please ask me any questions so i can clarify this more. I am also somewhat new to all this and apologize if I am not to clear.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Thanks everyone for your help. We are not there yet but you were all steering me in the right direction.

I have added a screen shot of the page for reference because the team was having issues making the given formula's work. I also combined the two sheets onto one page so there is less cross worksheet referencing and will be easier to read.

Cknum = Check#

解决方案

Two column matches typically come in one of two typical configurations; one being an array formula and the other being a standard formula. Both use variations of an INDEX/MATCH function pair.

        

The standard formula for Quickbooks!A2 would be,

=IFERROR(INDEX(Donations!A$1:A$999, MIN(INDEX(ROW($1:$999)+((Donations!B$1:B$999<>B2)+(Donations!C$1:C$999<>C2))*1E+99, , ))), "No match")

The array formula version for Quickbooks!A2 would be,

=IFERROR(INDEX(Donations!A$1:A$999, MATCH(1, (Donations!B$1:B$999=B2)*(Donations!C$1:C$999=C2), 0)), "no match")

Array formulas need to be finalized with Ctrl+Shift+Enter↵ rather than simply Enter↵.

Once one of the formulas has been put in Quickbooks!A2 correctly, fill down as necessary to catch the values from other rows. Note that I have changed the third line of your sample data to demonstrate a second DonationID lookup for Check# 106.

With ~50K records, there is the distinct possibility that multiple matches could be made on both columns B and C. To capture the subsequent second, third, etc matches from the Donations worksheet, change the MIN function to a SMALL function and use a progressive COUNTIFS function to adjust the k ordinal.

=IFERROR(INDEX(Donations!A$1:A$50000, SMALL(INDEX(ROW($1:$50000)+((Donations!B$1:B$50000<>B2)+(Donations!C$1:C$50000<>C2))*1E+99, , ), COUNTIFS(B$2:B2, B2, C$2:C2, C2))), "No match")

After setting up some intentional duplicates in the Donations worksheet like this,

DonationID  Check#  Amount
179         106    $200 
210         106    $500 
220         106    $600 
979         106    $200 
910         106    $500 
920         106    $600 

You should receive results like the following.

        

The IFERROR function has been used to catch non-matches and show no match rather than the #N/A error.

I have modified the values in your single worksheet sample data to show a variety of matches as well as one case where the chknum and amount were duplicated by two different donationID entries.

    

The matching records are color-coded for quick reference. I've made this sample workbook publically available at my Docs.com-Preview site.

 VLOOKUP_w_Multiple_Criteria_and_Duplicates.xlsx

这篇关于需要找到与两列条件匹配的值.可能的 VLOOKUP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！