How to match with it. Vlookup is not giving exact details.

M

Meenatshi

New Member
Joined
Dec 13, 2017
Messages
3
Hello,

Please have a look into the below two table. . and assume that 1st one is posting data and 2nd one applied data. I have to verify whether all the posting data are applied or not. If we use Vlookup or Duplicate formula that $2000 will show as applied but i have applied two only. one of the $2000 is missed to apply. . based on which formula i will get that report ?? please advise

[TABLE="width: 248"]
<tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]Account#[/TD]
[TD]Date[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77, align: right"]1010010000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="class: xl65, width: 73, align: right"]12/6/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl65, width: 98"]$38,125.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77, align: right"]1010010000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="class: xl65, width: 73, align: right"]12/6/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl65, width: 98"]$2,437.50[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77, align: right"]1010010000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="class: xl65, width: 73, align: right"]12/6/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl65, width: 98"]$2,000.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77, align: right"]1010010000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="class: xl65, width: 73, align: right"]12/6/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl65, width: 98"]$2,000.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77, align: right"]1010010000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="class: xl65, width: 73, align: right"]12/6/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl65, width: 98"]$2,000.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77, align: right"]1010010000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="class: xl65, width: 73, align: right"]12/6/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl65, width: 98"]$1,552.48[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77, align: right"]1010010000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="class: xl65, width: 73, align: right"]12/6/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl65, width: 98"]$1,002.01[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77, align: right"]1010010000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="class: xl65, width: 73, align: right"]12/6/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl65, width: 98"]$717.52[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500"]
<tbody>[TR]
[TD]Account#[/TD]
[TD]Date[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77, align: right"]1010010000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="width: 73, align: right"]12/6/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="width: 98"]$38,125.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77, align: right"]1010010000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="width: 73, align: right"]12/6/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="width: 98"]$2,437.50[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77, align: right"]1010010000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="width: 73, align: right"]12/6/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="width: 98"]$2,000.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77, align: right"]1010010000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="width: 73, align: right"]12/6/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="width: 98"]$2,000.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77, align: right"]1010010000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="width: 73, align: right"]12/6/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="width: 98"]$1,552.48[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77, align: right"]1010010000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="width: 73, align: right"]12/6/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="width: 98"]$1,002.01[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes

Unknown
ABC
13APPLIED TABLE
14Account#DateAmount
15101001000012-Jun-17$38,125.00
16101001000012-Jun-17$2,437.50
17101001000012-Jun-17$2,000.00
18101001000012-Jun-17$2,000.00
19101001000012-Jun-17$1,552.48
20101001000012-Jun-17$1,002.01
21
Sheet13




Unknown
ABCD
1POSTING DATA
2Account#DateAmountApplied/Not Applied
3101001000012-Jun-17$38,125.00Applied
4101001000012-Jun-17$2,437.50Applied
5101001000012-Jun-17$2,000.00Applied
6101001000012-Jun-17$2,000.00Applied
7101001000012-Jun-17$2,000.00Not Applied
8101001000012-Jun-17$1,552.48Applied
9101001000012-Jun-17$1,002.01Applied
10101001000012-Jun-17$717.52Not Applied
11
12
Sheet13
Cell Formulas
RangeFormula
D3=IF(COUNTIFS(A$3:A3,A3,B$3:B3,B3,C$3:C3,C3)<=COUNTIFS($A$15:$A$20,A3,$B$15:$B$20,B3,$C$15:$C$20,C3),"Applied","Not Applied")
 
Last edited:
Upvote 0
Hi If i use the formula, its showing something different. please help me out.

if possible could you please tell me your email id,

[TABLE="width: 504"]
<colgroup><col span="4"></colgroup><tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Account#[/TD]
[TD]Date[/TD]
[TD]Amount[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1010010000[/TD]
[TD]12-Jun-17[/TD]
[TD="align: right"]$38,125.00[/TD]
[TD]Batched[/TD]
[/TR]
[TR]
[TD]1010010000[/TD]
[TD]12-Jun-17[/TD]
[TD="align: right"]$2,437.50[/TD]
[TD]Batched[/TD]
[/TR]
[TR]
[TD]1010010000[/TD]
[TD]12-Jun-17[/TD]
[TD="align: right"]$2,000.00[/TD]
[TD]Not Batched[/TD]
[/TR]
[TR]
[TD]1010010000[/TD]
[TD]12-Jun-17[/TD]
[TD="align: right"]$2,000.00[/TD]
[TD]Not Batched[/TD]
[/TR]
[TR]
[TD]1010010000[/TD]
[TD]12-Jun-17[/TD]
[TD="align: right"]$2,000.00[/TD]
[TD]Not Batched[/TD]
[/TR]
[TR]
[TD]1010010000[/TD]
[TD]12-Jun-17[/TD]
[TD="align: right"]$1,552.48[/TD]
[TD]Batched[/TD]
[/TR]
[TR]
[TD]1010010000[/TD]
[TD]12-Jun-17[/TD]
[TD="align: right"]$1,002.01[/TD]
[TD]Batched[/TD]
[/TR]
[TR]
[TD]1010010000[/TD]
[TD]12-Jun-17[/TD]
[TD="align: right"]$717.52[/TD]
[TD]Not Batched[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Account#[/TD]
[TD]Date[/TD]
[TD]Amount[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1010010000[/TD]
[TD]12-Jun-17[/TD]
[TD="align: right"]$38,125.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1010010000[/TD]
[TD]12-Jun-17[/TD]
[TD="align: right"]$2,437.50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1010010000[/TD]
[TD]12-Jun-17[/TD]
[TD="align: right"]$2,000.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1010010000[/TD]
[TD]12-Jun-17[/TD]
[TD="align: right"]$2,000.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1010010000[/TD]
[TD]12-Jun-17[/TD]
[TD="align: right"]$1,552.48[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1010010000[/TD]
[TD]12-Jun-17[/TD]
[TD="align: right"]$1,002.01[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

=IF(COUNTIFS(A$3:A3,A3,B$3:B3,B3,C$3:C3,C3)<=COUNTIFS(A14:A19,A3,B14:B19,B3,C14:C19,C3),"Batched", "Not Batched")
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
sorting duplicates Customers names by add two formulas in last column
Replies
3
Views
374
Kerryx
K
skyh3ck
  • Question Question
1st match column header, 2nd lookup a value in same column, 3rd return corresponding value from the very first column ?? need help
Replies
2
Views
571
skyh3ck
skyh3ck
Q
  • Solved
Alternatives for searching data from parameters
Replies
7
Views
809
Peter_SSs
Peter_SSs
J
  • Question Question
Helps with VLOOKUP formula with correct column matching if a date is given
Replies
5
Views
544
Peter_SSs
Peter_SSs
M
  • Question Question
Spread revenue across month
Replies
3
Views
1K
manshah
M

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

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