Matching info in 2 columns with another tabbed sheet and highlighting???

T

Turbo68

Board Regular
Joined
Jan 31, 2014
Messages
118
I need some guidance on how I can take info from one tab in a workbook and check 2 columns for matching data in another tab. Then if it finds a match for both on the same line it would note that someway.


My first Tab sheet looks like this:

ABCDEFG
DateChargedVendorto ChargeDescription of Item(s) PurchasedUse of Item(s) PurchasedOn Statement
1/20/2014 $ 53.89PDQ25Fuel
1/20/2014 $ 53.86Hays 24/725Fuel
1/20/2014 $ 47.84Valero #407424Fuel
1/21/2014 $ 97.07Quality Inn25Lodging
1/21/2014 $ 46.71Home Depot22Misc
1/22/2014 $ 86.12Quality Inn21Lodging
1/22/2014 $ 60.34Craig Conoco25Fuel
1/23/2014 $ 81.66Sleep Inn25Lodging
1/23/2014 $ 55.07Davis Bros Service24Fuel
1/23/2014 $ 49.43Pilot # 90322Fuel
1/24/2014 $ 81.50Quality Inn24Lodging
1/28/2014 $ 50.49Caseys General25Fuel
1/30/2014 $ 52.80EZ GO 7125Fuel
1/30/2014 $ 107.14Jiffy Lube25Misc

<tbody>
</tbody>


My Second Tab looks like this:

ABCDEFG
Originating Account NumberPosting DateTrans DateTypeDescriptionExpense CategoryAmount
xxxx-xxxx-xxxx-20961/13/20141/9/2014DebitHotelTravel$81.50
xxxx-xxxx-xxxx-20961/14/20141/13/2014DebitFuelAuto Related$56.06
xxxx-xxxx-xxxx-20961/16/20141/15/2014DebitFuelAuto Related$52.59
xxxx-xxxx-xxxx-20961/22/20141/20/2014DebitFuelAuto Related$53.89
xxxx-xxxx-xxxx-20961/22/20141/20/2014DebitFuelAuto Related$53.86
xxxx-xxxx-xxxx-20961/22/20141/20/2014DebitFuelAuto Related$47.84
xxxx-xxxx-xxxx-20961/23/20141/21/2014DebitHome DepotHome Repair$46.71
xxxx-xxxx-xxxx-20961/23/20141/21/2014DebitHotelTravel$97.07
xxxx-xxxx-xxxx-20961/23/20141/22/2014DebitFuelAuto Related$60.34
xxxx-xxxx-xxxx-20961/24/20141/22/2014DebitHotelTravel$86.12
xxxx-xxxx-xxxx-20961/24/20141/23/2014DebitFuelAuto Related$49.43
xxxx-xxxx-xxxx-20961/26/20141/23/2014DebitFuelAuto Related$55.07
xxxx-xxxx-xxxx-20961/26/20141/23/2014DebitHotelTravel$81.66
xxxx-xxxx-xxxx-20961/26/20141/24/2014DebitHotelTravel$81.50
xxxx-xxxx-xxxx-20961/30/20141/28/2014DebitFuelAuto Related$50.49

<tbody>
</tbody>


Now.. in the first TAB above in Column "G" i would like it to check if the amount in B and date in A and find that line in the second Tab in columns G (for the amount) and C (for the date).
Then if it finds a match it would let me know YES or NO in column G in the first TAB.

I have tried this with vlookup but there is no value to check for. I think a massive if then could work but this list will grow with time and needs to check the entire data for matches.

I hope i explained this well enough so someone might lead me to a solution.

Thanks in advance.


Any help will be appreciated.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
This ARRAY formula will give you the match, but based on your data, I cannot find any matches for both the date and the amount?

=MATCH(A2&B2,Sheet2!$B$2:$B$16&Sheet2!$G$2:$G$16,0)

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
 
Upvote 0
FDibbins said:
This ARRAY formula will give you the match, but based on your data, I cannot find any matches for both the date and the amount?

=MATCH(A2&B2,Sheet2!$B$2:$B$16&Sheet2!$G$2:$G$16,0)

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Click to expand...



I only get #N/A with that. If you look there is match from the first tab first line with the data = 1/20/2014 and $53.89 and the match in the 2nd tab on the 4th line down with the same 1/20/2014 and $53.89. That is the matches I am need to get so to know what is still outstanding.

Does that make more sense?

Thank you
 
Upvote 0
OK I was using the Posting date (B). Change that reference to C...
=MATCH(A2&B2,Sheet2!$C$2:$C$16&Sheet2!$G$2:$G$16,0)
 
Upvote 0
Will I did make that change and still getting "#N/A". Guess I will keep trying

Thanks.
 
Upvote 0
Did you ARRAY enter that? CTRL SHFT ENTER, not just enter....
=MATCH(A2&B2,Sheet2!$C$2:$C$16&Sheet2!$G$2:$G$16,0)

A​
B​
C​
D​
E​
F​
G​
1​
DateChargedVendorto ChargeDescription of Item(s) PurchasedUse of Item(s) PurchasedOn Statement
2​
1/20/2014$53.89 PDQ25Fuel4
3​
1/20/2014$53.86 Hays 24/725Fuel5
4​
1/20/2014$47.84 Valero #407424Fuel6
5​
1/21/2014$97.07 Quality Inn25Lodging8
6​
1/21/2014$46.71 Home Depot22Misc7
7​
1/22/2014$86.12 Quality Inn21Lodging10
 
Upvote 0
You must log in or register to reply here.

Similar threads

Sufiyan97
  • Locked
  • Question
Lookup Partial Text
Replies
1
Views
717
Fluff
Fluff
Sufiyan97
  • Question
Partial Text Lookup
2
Replies
18
Views
2K
Sufiyan97
Sufiyan97
D
  • Question
Move data from one sheet to mutlipe sheets based on sheet name
Replies
3
Views
509
Herakles
H
S
  • Question
VBA: one to many relationship - 2 Columns of Data from different Sheets
Replies
2
Views
275
Stranger8421
S
A
  • Solved
replace column with another based on match column between two sheets
Replies
7
Views
388
JoeMo
J

Forum statistics

Threads
1,215,268
Messages
6,123,970
Members
449,137
Latest member
yeti1016

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