Multiple Matches but only pulling 1st one

C

CrashOD

Board Regular
Joined
Feb 5, 2019
Messages
118
Office Version
  1. 2021
  2. 2016
Platform
  1. Windows
tried doing this codes looking for
C12 ' is the value i'm wanting to search for were they can be multi matches if there is i need B44-B46, D44-D46 have values in there
'SK Real Estate'!$U:$U ' is the location to look for the info to match to (C12)

B44 ' should show SK Real Estate'!$H:$H
'SK Real Estate'!$H:$H ' (Date) is the value i want returned if there is a value

B45 ' should show 'SK Real Estate'!$J:$J
'SK Real Estate'!$J:$J ' (Date) is the value i want returned if there is a value

B46 ' should show 'SK Real Estate'!$L:$L
'SK Real Estate'!$L:$L ' (Date) is the value i want returned if there is a value

D44 ' should show SK Real Estate'!$I:$I
'SK Real Estate'!$I:$I ' (Amount) is the value i want returned if there is a value

D45 ' should show 'SK Real Estate'!$K:$K
'SK Real Estate'!$K:$K ' (Amount) is the value i want returned if there is a value

D46 ' should show 'SK Real Estate'!$M:$M
'SK Real Estate'!$M:$M ' (Amount) is the value i want returned if there is a value

I tried the below codes with no luck. B44 & D44 get filled in but nothing more. on the picture below with the black boxes line 1170 in $U matches for the 2nd time witch should fill in B45 & d45 for 2nd payment.

=IF(ISNUMBER(MATCH('SK Real Estate'!$A:$U, $C$12)), MATCH($C$12('SK Real Estate'!$A:$U), ROW('SK Real Estate'!$A:$U)), "")

=INDEX('SK Real Estate'!$A:$U,MATCH(C12,'SK Real Estate'!$U:$U,0),8)

=IFERROR(INDEX('SK Real Estate'!$H:$H, SMALL(IF($C$12='SK Real Estate'!$U:$U, ROW('SK Real Estate'!$U:$U)-ROW('SK Real Estate'!$U)+1), ROW(1:1))),"" )


this picture shows were im pulling data from (this exact sheet in in the same workbook im looking to put code in. why i used 'SK Real Estate'! and not reference a workbook then sheet
excel.png



the picture below is were im doing the code for B44:B46 & D44:d46. yes the total due is wrong for face changing code to pull the other amount on sheet should be
321.41​
327.97​
360.77​

1654277725194.png


Thanks so much for any help!
 
Sort by date Sort by votes
That sounds like an ambitious project....glad you have it working. See my post #28 for a comment about what was then the D43 formula. That appears to now be the formula in D31 and D47. That formula can be tightened up:
Excel Formula: 
=IF(D28<=SUM(D32:D34),0,IF(SUM(D28,-D32:D34)<=0.02,0,IF(D28>=SUM(D32:D34),SUM(D28,-D32:D34)),"help")))
but check the logic in this formula because you have one condition for D28<=sum and another for D28>=same sum. One of those should exclude the other for the case where D28=same sum. So you will probably want an < (not <=) on the first, or a > (not >=) on the other...but I'm not sure what your conditions should look like.

Then also review these:
E33,E49E33=IF(C33="","",IF(D33=0,"UNPAID",IF(D33<=D32,"FACE",IF(D32>D33,"PENALTY"))))
E34,E50E34=IF(C34="","",IF(D34=0,"UNPAID",IF(D34<=D32,"FACE",IF(D32>D34,"PENALTY"))))
The red and green parts say nearly the same thing (with the exception of when they are equal)...which is not what you want. I'm not sure what these are checking for, but you will want one IF checking for a particular condition and if that condition is not met, the other expression takes over. But you may not even need that other IF conditional...you could simply replace the return-if-FALSE part of the function with "PENALTY"...for example:
Excel Formula: 
=IF(C33="","",IF(D33=0,"UNPAID",IF(D33<=D32,"FACE","PENALTY")))
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Automated 2022 Tax Certification Form.xlsm
HIJKLMNOPTU
271010/29/2021$21.7909/15/2021$21.7910/18/2021$21.7964.07$21.7971.921810013-02-005-317
SK Real Estate 2021
Cells with Conditional Formatting
CellConditionCell FormatStop If True
U2710:U2711Cell ValueduplicatestextNO


Automated 2022 Tax Certification Form.xlsm
ABCDE
42SCHOOL 2021July/AugSept/OctNov/Dec
43Discount (-2%)Face:Penalty (+10%)
44PAID$64.07$65.38$71.92
45Date Paid:9/15/21HomeSteadAssessed Value
46NO1,810
47PARTIALSDATE$65.38<- SHORT IN FACE
48PAYMENT #101/00/1900$0.00FACE
49PAYMENT #2   
50PAYMENT #3   
Tax Cert Form 2022-2021
Cell Formulas
RangeFormula
C44C44=INDEX('SK Real Estate 2021'!$U$2:$AK$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),11)
D44D44=INDEX('SK Real Estate 2021'!$U$2:$AK$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),12)
E44E44=INDEX('SK Real Estate 2021'!$U$2:$AK$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),13)
B44B44=IF(B45="","UNPAID",IF(B45="EXONERATED","PER COUNTY",IF(B45="Tax Claims", "SHORT", "PAID")))
B45B45=IF(D47<=0,C50,IF(D44>D47,"Tax Claims",IF(INDEX('SK Real Estate 2021'!$C$2:$U$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),1)="","",INDEX('SK Real Estate 2021'!$C$2:$U$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),1))))
E46E46=INDEX('SK Real Estate 2021'!$T$2:$U$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),1)
D46D46=INDEX('SK Real Estate 2021'!$U$2:$AI$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),15)
D47D47=IF(D48="","",IF(D44<=SUM(D48,D49,D50),0,IF(SUM(D44,-N(D48),-N(D49),-N(D50))<0.02,0,IF(D44>SUM(D48,D49,D50),SUM(D44,-N(D48),-N(D49),-N(D50)),"help"))))
C48C48=IFERROR(IF(ROWS(C$48:C48)<=COUNTA(INDEX('SK Real Estate 2021'!$H$2:$M$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),))/2,INDEX('SK Real Estate 2021'!$H$2:$M$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),2*ROWS(C$48:C48)-1),""),"")
D48D48=IFERROR(IF(ROWS($D$48:D48)<=COUNTA(INDEX('SK Real Estate 2021'!$H$2:$M$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),))/2,INDEX('SK Real Estate 2021'!$H$2:$M$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),2*ROWS($D$48:D48)-1),""),"")
E48E48=IF(C48="","", "FACE")
C49:C50C49=IFERROR(IF(ROWS($C$48:C49)<=COUNTA(INDEX('SK Real Estate 2021'!$H$2:$M$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),))/2,INDEX('SK Real Estate 2021'!$H$2:$M$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),2*ROWS($C$48:C49)-1),""),"")
D49:D50D49=IFERROR(IF(ROWS($D$48:D49)<=COUNTA(INDEX('SK Real Estate 2021'!$H$2:$M$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),))/2, INDEX('SK Real Estate 2021'!$H$2:$M$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),2*ROWS($D$48:D49)),""),"" )
E49E49=IF(C49="","",IF(D49>=D48,"FACE",IF(D48<D49,"PENALTY")))
E50E50=IF(C50="","",IF(D50>=D48,"FACE",IF(D48<D50,"PENALTY")))


I changed name of tab to add the year and fixed code for new name but bring up a weird date and amount and this person has 3 payments.
 
Upvote 0
Yes..you are not being careful when editing the formulas. Your C48 formula no longer has the ROWS reference "fixed"...it says =IFERROR(IF(ROWS(C$48:C48)<= rather than =IFERROR(IF(ROWS($C$48:C48)<=. The same issue occurs at the end of that formula too.
And then you've overwritten the D46 formula such that the last part of the formula will not return the correct columns. The D formulas do not have the -1 in the last part of the formula.
I also see that you haven't reviewed my last post #31 where I called attention to the column E formulas. What you have will produce erroneous results (you will never get "PENALTY" to display, as it is currently written).
 
Last edited:
Upvote 0
Automated 2022 Tax Certification Form.xlsm
ABCDE
42SCHOOL 2021July/AugSept/OctNov/Dec
43Discount (-2%)Face:Penalty (+10%)
44PAID$64.07$65.38$71.92
45Date Paid:9/15/21HomeSteadAssessed Value
46NO1,810
47PARTIALSDATE$65.38<- SHORT IN FACE
48PAYMENT #101/00/1900$0.00FACE
49PAYMENT #2   
50PAYMENT #3   
Tax Cert Form 2022-2021
Cell Formulas
RangeFormula
C44C44=INDEX('SK Real Estate 2021'!$U$2:$AK$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),11)
D44D44=INDEX('SK Real Estate 2021'!$U$2:$AK$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),12)
E44E44=INDEX('SK Real Estate 2021'!$U$2:$AK$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),13)
B44B44=IF(B45="","UNPAID",IF(B45="EXONERATED","PER COUNTY",IF(B45="Tax Claims", "SHORT", "PAID")))
B45B45=IF(D47<=0,C50,IF(D44>D47,"Tax Claims",IF(INDEX('SK Real Estate 2021'!$C$2:$U$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),1)="","",INDEX('SK Real Estate 2021'!$C$2:$U$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),1))))
E46E46=INDEX('SK Real Estate 2021'!$T$2:$U$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),1)
D46D46=INDEX('SK Real Estate 2021'!$U$2:$AI$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),15)
D47D47=IF(D48="","",IF(D44<=SUM(D48,D49,D50),0,IF(SUM(D44,-N(D48),-N(D49),-N(D50))<0.02,0,IF(D44>SUM(D48,D49,D50),SUM(D44,-N(D48),-N(D49),-N(D50)),"help"))))
C48:C50C48=IFERROR(IF(ROWS($C$48:C48)<=COUNTA(INDEX('SK Real Estate 2021'!$H$2:$M$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),))/2,INDEX('SK Real Estate 2021'!$H$2:$M$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),2*ROWS($C$48:C48)-1),""),"")
D48:D50D48=IFERROR(IF(ROWS($D$48:D48)<=COUNTA(INDEX('SK Real Estate 2021'!$H$2:$M$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),))/2,INDEX('SK Real Estate 2021'!$H$2:$M$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),2*ROWS($D$48:D48)),""),"")
E48E48=IF(C48="","", "FACE")
E49:E50E49=IF(C49="","",IF($D$48>=D49,"FACE",IF($D$48<D49,"PENALTY")))


for the penalty i thought i fixed that unless i did that on a different sheet that someone else uses.
 
Upvote 0
That latest version appears to be correct. Is it working okay now? Because many of these formulas can be messy to maintain (and it is easy to accidentally overwrite or delete something that should not be modified), I've found the best way to make certain types of changes is to use Home > Replace and then type, for example SK Real Estate 2020 in the "Find What" field and SK Real Estate 2021 in the "Replace with" field. And then further options allow for fine-tuning the replacement. My menu gives me the option to make the changes within the Sheet that is active and to Look in Formulas. So when I click "Replace All", all instances in a formula where I have a hardwired sheet name of SK Real Estate 2020 would be replaced with SK Real Estate 2021...and no further editing of the formulas is necessary. But if you do this, be careful of unintended consequences, as the text string you search for might be present in some places/contexts that should be left untouched. For that reason, I usually step through the sheet, examine the formulas and determine a text string that is unique so that only those text strings that I want to change will be affected.
 
Upvote 0
016-01-008-014

when i used that number it showed the right date and amount but nothing in the next two. but that first one does not show right

=FILTERXML(
shows up but only that filter.
 
Upvote 0
I don't understand. Can you post more details showing where 016-01-008-014 occurs in SK Real Estate 2021 and what results are produced on the Tax Cert Form worksheet?

Where are you using FILTERXML?
 
Upvote 0
Automated 2022 Tax Certification Form.xlsm
ABCDEFGHIJKLMNOPTU
270710/29/2021 #28,658.69
2708RUNKEL JUDITH R 0160013710/29/2021F44528/3/21$80.688/30/21$80.6610/29/21$80.6680.66266.206700016-01-008-014
2709DIORIA CHARLES L JR/CHRISTINA 0110006410/29/2021F10217/30/21$161.479/14/21$161.4710/29/21$161.45474.68$161.45532.8115550011-01-003-135
2710TRONCOSO MARIE/ERUIN 0130070310/18/2021F141PAIDTODAY10/29/2021:MISSINGFIRSTPAYMENT10/29/21$21.799/15/21$21.7910/18/21$21.7964.07$21.7971.921810013-02-005-317
271110/29/2021 #2$263.90
SK Real Estate 2021
Cell Formulas
RangeFormula
O2707O2707=SUM(O2663:O2706)
O2711O2711=SUM(O2708:O2710)


Automated 2022 Tax Certification Form.xlsm
ABCDE
42SCHOOL 2021July/AugSept/OctNov/Dec
43Discount (-2%)Face:Penalty (+10%)
44PAID$64.07$65.38$71.92
45Date Paid:9/15/21HomeSteadAssessed Value
46NO1,810
47PARTIALSDATE$65.38<- SHORT IN FACE
48PAYMENT #101/00/1900$0.00FACE
49PAYMENT #2   
50PAYMENT #3   
Tax Cert Form 2022-2021
Cell Formulas
RangeFormula
C44C44=INDEX('SK Real Estate 2021'!$U$2:$AK$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),11)
D44D44=INDEX('SK Real Estate 2021'!$U$2:$AK$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),12)
E44E44=INDEX('SK Real Estate 2021'!$U$2:$AK$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),13)
B44B44=IF(B45="","UNPAID",IF(B45="EXONERATED","PER COUNTY",IF(B45="Tax Claims", "SHORT", "PAID")))
B45B45=IF(D47<=0,C50,IF(D44>D47,"Tax Claims",IF(INDEX('SK Real Estate 2021'!$C$2:$U$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),1)="","",INDEX('SK Real Estate 2021'!$C$2:$U$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),1))))
E46E46=INDEX('SK Real Estate 2021'!$T$2:$U$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),1)
D46D46=INDEX('SK Real Estate 2021'!$U$2:$AI$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),15)
D47D47=IF(D48="","",IF(D44<=SUM(D48,D49,D50),0,IF(SUM(D44,-N(D48),-N(D49),-N(D50))<0.02,0,IF(D44>SUM(D48,D49,D50),SUM(D44,-N(D48),-N(D49),-N(D50)),"help"))))
C48:C50C48=IFERROR(IF(ROWS($C$48:C48)<=COUNTA(INDEX('SK Real Estate 2021'!$H$2:$M$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),))/2,INDEX('SK Real Estate 2021'!$H$2:$M$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),2*ROWS($C$48:C48)-1),""),"")
D48:D50D48=IFERROR(IF(ROWS($D$48:D48)<=COUNTA(INDEX('SK Real Estate 2021'!$H$2:$M$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),))/2,INDEX('SK Real Estate 2021'!$H$2:$M$7000,MATCH($C$12,'SK Real Estate 2021'!$U$2:$U$7000,0),2*ROWS($D$48:D48)),""),"")
E48E48=IF(C48="","", "FACE")
E49:E50E49=IF(C49="","",IF($D$48>=D49,"FACE",IF($D$48<D49,"PENALTY")))


c12=
013-02-005-317
 
Upvote 0
When I paste your data and worksheet sample into my own, I get the correct results...no adjustments needed. Try manually retyping the C12 entry. I suspect the value in C12 may appear to be a match but it might have an extra space or hidden character that prevents a match. But before overwriting the cell, enter this formula in an empty cell =LEN(C12) and let me know how long it says the text string is.
 
Upvote 0
14
i did a replace on the whole column to remove any spaces prior since a lot of are cells from the company that made it has spaces were there should not be.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
3 payment some payments not made or paid in full
Replies
1
Views
411
CrashOD
C
C
  • Question
#Value! error when adding cells and - and is neg number
Replies
2
Views
426
CrashOD
C
C
  • Question
Coding for if then statement not working right
Replies
3
Views
392
CrashOD
C
C
  • Question
name(partial or not) and searches all the other sheets in workbook
Replies
0
Views
328
CrashOD
C
V
  • Solved
Incremental count of unique values from a dynamically generated list.
Replies
6
Views
356
vampsthevampyre
V

Forum statistics

Threads
1,215,949
Messages
6,127,880
Members
449,411
Latest member
AppellatePerson

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top