You need to find a value that matches two columns of criteria. Possible VLOOKUP
Update below - 6/4
I have two sheets in excel. One is a list of donors with their check number # / amount / Donor ID ("donation" sheet), and the other is a copy of account information with Donor ID / number / check # / amount (worksheet). There is no completed DonorDid in Quickbooks yet.
The problem is that I need to match the Donor ID to their checks. To get this I need to match check#
and amount
in "Quickbooks" the same in "Donations", when they match, this will give me the Donor ID matching this check.
Here's how it's 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 No. 106 for $ 200," he has to tell me that it is from Donor 179.
Some checks are not the same and not from donors. The list has about 50,000 names.
Please ask me any questions so I can clarify this. I'm also a bit new to all of this and apologize if I don't clean up.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~
Thanks everyone for your help. We're not there yet, but you have all steered me in the right direction.
I added a screenshot of the page for reference because the team was having problems making this formula work. I also merged the two sheets into one page so there are fewer cross-referenced links and easier to read.
Cknum = Check #
source to share
Two column matches usually come in one of two typical configurations; one of which is an array formula and the other is a standard formula. Both use the INDEX / MATCH options .
The standard formula for Quickbooks! A2 will,
=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 Quickbooks version of the array formula! A2 will,
=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 should be completed with Ctrl+ Shift+ Enter↵, not just Enter↵.
Once one of the formulas has been correctly inserted into Quickbooks! A2, fill in if necessary to catch values from other lines. Please note that I have changed the third line of your sample data to demonstrate a second donation search for verification # 106.
With ~ 50K entries, there is a clear possibility that multiple matches could be made in both columns B and C. To record subsequent seconds, third, etc. on a sheet of donations, change the MIN function in the SMALL function , and use progressive function COUNTIFS , to adjust the sequence number k.
=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 on the Donations sheet, for example
DonationID Check# Amount
179 106 $200
210 106 $500
220 106 $600
979 106 $200
910 106 $500
920 106 $600
You should get the results as shown below.
The IFERROR function was used to catch inconsistencies and did not display a match, not an error #N/A
.
I changed the values in your sample single sheet data to show many matches, and also one case where chknum and amount were duplicated by two different donationID records.
The corresponding entries are color-coded for quick reference. I made this example workbook publicly available on my Docs.com-Preview site.
source to share
There is an existing DGET command that can be used for multiple criteria.
In your QB sheet, I added some more lines starting at A7.
ID CheckNo Amount
106 200
The ID is 179
Please note that I changed Check # to CheckNo. I also added a definition donationDB
, A1 to D4.
The DGet operator is number 179. I used =DGET(donationDB,Donations!B1,Quickbooks!A7:C8)
What DGET does for database lookup is tell that you are looking for Donor ID in B1 using the criteria in fields A7 to C8.
source to share
One quick workaround for this problem is to create a pivot table in the data Donations
and then use it GETPIVOTDATA
to pull values from it to get the donation ID in the table Quickbooks
.
This is easy to do if the donation ID is always numeric. You set up a pivot table with row fields including number and number of checks. Then you set the values to equal MIN
or MAX
donation id. If there is only one result, you will receive it immediately. If there is a collision, you can determine it by switching to COUNT
and check any of these >1
.
After the table is set up with MAX
, you can quickly use GETPIVOTDATA
to output the corresponding value.
This assumes that your values amount
are close enough (in floating point decimal) to match. Almost everything will be. Some may not. If you can get 50k records up to 100, though for double checking, then life is good.
Image of sample data and Pivot setup , showing pivot table built on left data (with donation ID). The search data is on the right.
The formula for the search is simple GETPIVOTDATA
, it's in a cell F3
and copied down.
=GETPIVOTDATA("Donation",$B$10,"Check",G3,"Amount",H3)
You will get an error #REF!
if the match fails. See the last line for an example.
source to share
To compare two datasets by matching multiple fields and getting another field as a result of matching, I suggest adding a new field to each database to create the combined value of the fields to be matched, then use VLOOKUP
and COUNTIF
.
Lets use example data provided by Jeeped in which we have: Data A: Quickbooks - range D1:M24
and Data B: Donations - range R1:W23
to be expanded to C1:M24
and Q1:W23
accordingly
- Add Key field to combine fields to match (2 fields in this case, but also works for more), use
|
as separator.
Data A: Add "Key" field in column C
and enter this formula
=CONCATENATE($E2,"|",$M2)
Data B: Add "Key" field in column Q
and enter this formula
=CONCATENATE($V2,"|",$W2)
- Enter formulas to match the concatenated fields and get the information you want (also indicates duplicate items)
Data A: Add this formula to the 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),""))
Data B: add this formula to 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),""))
source to share
I am using a function AVERAGEIFS
to get a numeric value based on multiple criteria. If you are not sure if the criteria will only return one value, you can include in the statement COUNTIFS
.
=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.
source to share