需要找到匹配两列条件的值。可能的VLOOKUP [英] Need to find a value that matches two columns of criteria. Possible VLOOKUP
问题描述
更新以下 - 6/4
我有两个Excel工作表。一个是捐助者的列表，其支票号为＃/ amount / Donor ID（捐款）工作表，另一个是捐助者ID /支票＃/金额（ quickbooks 工作表）。 Quickbooks尚未填写DonorID。
我遇到的问题是我需要用支票来配对捐助者ID。为了得到这一点，我需要将Quickbooks中的 check＃和金额匹配到捐款中，当他们匹配时，它会给我与该支票相对应的捐助者ID。
这是如何布置的：
捐款工作表：
ABC
捐款ID检查＃金额
1 179 106 $ 200
2 210 106 $ 500
3 220 106 $ 600
Quickbooks工作表：
ABC
捐款ID检查＃金额
1不适用106 200
2 N / a 1074 500
3 N / a 300 1000
当我要求查找检查＃106是$ 200应该告诉我，这是从捐赠者179.
一些支票不匹配，不是来自捐赠者。该列表有近50000个名字。
请问有任何问题，以便我可以澄清这一点。我也有点新鲜，如果我不清楚，道歉。
~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
感谢大家的帮助。我们还没有，但是你们都是正确地指导我。
我已经添加了一个屏幕截图的页面供参考，因为团队有问题使给定公式的工作。我也将这两张纸合并在一个页面上，所以交叉工作表引用较少，更容易阅读。
Cknum =检查＃ / p>
两个列匹配通常来自两个典型配置之一;一个是数组公式，另一个是标准公式。两者都使用到已被用于捕获非匹配，并显示不匹配而不是 #N / A 错误。
我已经修改了哟的值ur单工作表样本数据显示各种匹配，以及一种情况，其中 chknum 和金额由两个不同的捐赠ID 条目复制。
匹配的记录是颜色编码的，以供快速参考。我已经在我的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屋！
