excel - Need to find a value that matches two columns of criteria. Possible VLOOKUP

Question

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#

Answer 1

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