CODEWITHSUNDEEP
excelexcel-formula
Elia
Elia

Reputation: 43

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

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#

worksheet

Upvotes: 4

Views: 1453

Answers (5)

CJB
CJB

Reputation: 1809

I use the AVERAGEIFS function for getting a numeric value based on multiple criteria. If you are not confident that the criteria would only return one value, then you can wrap in a COUNTIFS statement.

=IF(COUNTIFS($A$1:$A$10, crit1, $B$1:$B$10, crit2) = 1, AVERAGEIFS($C$1:C$10, $A$1:$A$10, crit1, $B$1:$B$10, crit2), #N/A)

This only works for numeric values though.

Upvotes: 1

EEM
EEM

Reputation: 6659

To compare two sets of data by matching several fields and obtain another field as a result of the match, I suggest to add a new field to each database to create a concatenated value of the Fields to match, then use a VLOOKUP and COUNTIF formulas combined.

Let’s use the sample data provided by Jeeped, in which we have: Data A: Quickbooks - range D1:M24 and Data B: Donations – range R1:W23, to be extended to C1:M24 and Q1:W23 respectively

  1. Add Field “Key” to concatenate the Fields to match (2 fields in this case, but also works for more), use | as separator.

Data A: Add Field “Key” in Column C and enter this formula

=CONCATENATE($E2,"|",$M2)

Data B: Add Field “Key” in Column Q and enter this formula

=CONCATENATE($V2,"|",$W2)
  1. Enter formulas to match concatenated fields and retrieve required information (also indicates items duplicated)

Data A: Add this formula in column N

=IF(COUNTIF($Q$1:$Q$23,$C2)>1,
"Duplicated: "&COUNTIF($Q$1:$Q$23,$C2),
IFERROR(VLOOKUP($C2,$Q$1:$W$23,2,0),""))

enter image description here

Data B: Add this formula in column X

=IF(COUNTIF($C$1:$M$24,$Q5)>1,
"Duplicated: "&COUNTIF($C$1:$M$24,$Q5),
IFERROR(VLOOKUP($Q5,$C$1:$M$24,2,0),""))

enter image description here

Upvotes: 1

user4039065
user4039065

Reputation:

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.

        Two column Match with INDEX MATCH

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.

        Two column Matches with duplicates

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.

    VLOOKUP multiple criteria with duplicates

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

Upvotes: 3

Byron Wall
Byron Wall

Reputation: 4010

One quick way out of this problem is to create a Pivot Table on the Donations data and then use GETPIVOTDATA to pull values out of it to get the donation id on the Quickbooks table.

This is easily done if the donation ID is always numeric. You set up a Pivot Table with row fields including check number and amount. You then set the values to be equal to MIN or MAX of the donation ID. If there is only one result you will get it immediately. If there is a clash, you can determine this by switching over to COUNT and flag any of those >1.

Once you have the table set up with MAX you can then quickly use GETPIVOTDATA to pull in the matching value.

This assumes that your amount values are close enough (in the floating point decimal sense) to match. Nearly all will be. Some may not. If you can get 50k records down to 100 though to double check, then life is good.

Picture of sample data and Pivot set up, shows the Pivot Table built on the left data (with the donation ID). The lookup data is on the right.

data and pivot

Formula for the lookup is a simple GETPIVOTDATA, this is in cell F3 and copied down.

=GETPIVOTDATA("Donation",$B$10,"Check",G3,"Amount",H3)

You will get the #REF! error if a match is not made. See the last row for an example.

Upvotes: 1

rajah9
rajah9

Reputation: 12339

There is an existing command, DGET, that can be used for multiple criteria.

In your QB worksheet, I added another few rows, starting at A7. 

ID      CheckNo  Amount
        106      200

    The ID is   179

Note that I changed Check# to CheckNo. I also added a definition of donationDB, from A1 to D4.

The DGet statement is where the 179 is. I used =DGET(donationDB,Donations!B1,Quickbooks!A7:C8)

What the DGET does is to look up the database, tell it you're looking for the Donor ID in B1, using the criteria in the fields A7 to C8.

Upvotes: 1

Related Questions