Index and match with date range

Dausbrooks

New Member
Joined
Feb 5, 2020
Messages
1
Office Version
  1. 2010
Platform
  1. Windows
I have two separate spreadsheets for my business. My sales spreadsheet lists all services by date with the revenue charged including associated taxes and tips. I get a separate spreadsheet that lists all credit card transactions which contains the date of transaction and total revenue but also the associated credit card fees. My issue is the date of transaction could differ from the date of service. It could lag up to three days.

My simplified Credit Card Charges spreadsheet:
1580926965495.png


My simplified Sales spreadsheet with Column H and I added.
1580926691791.png


I was considering using an Index & Match to pull my credit card fees to the sales spreadsheet.

Assumptions:
I have multiple sales on a given day. (up to 20) therefore I rarely have the matching revenue charges within a three day period.
The credit card charges typically fall within 3 days of the actual date of service.
Dates in spreadsheet are numerical.


My goal is to have a running list of deposits for any given day that is accurate within the assumptions.

My thought was to index by using an inserted column which combines columns one and two of each spreadsheet i.e. Sales!A2&B2 with Credit_transactions!A2&B2 However I am unsure how I can look for a range using the Index and Match functions. Perhaps there is a much simpler means that I am unaware?
 

Attachments

  • 1580926665166.png
    1580926665166.png
    7.4 KB · Views: 6
  • 1580926965358.png
    1580926965358.png
    6.9 KB · Views: 6
  • 1580926965441.png
    1580926965441.png
    6.9 KB · Views: 7

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Hi Dausbrooks

The basic index match formula can use cell references or Named Ranges.
Personally, I deal with lots of worksheets (ws) that change range every time they are used, so I opt to make every table a dynamic table.
Then, when I set Named Ranges for a column of data, the range covers the size of the table. Thus, as the table grows in data, the Named Range still covers all data.

For instance, lets say I select cell A1. Then I hold down the shift and ctrl keys, I hit the right cursor and then the down cursor. This selects all data in my table.
Then I go to the Format as Table, and create a table with headers (important). Then I click a cell within the table and go to the Design tab. In the top left of the design tab, I rename my table e.g., SalesTable (by clicking in the Table Name box, typing a table name, and hitting the enter key).

Why You Should Be Using Named Ranges in Excel

To create a named range covering A2 to bottom of the table, I do the following: a) select cell A2 b) hold down the shift and ctrl keys, I hit the down arrow key (this selects all cells in column A in SalesTable). Then I go to the Name Box (see above link) and type in a name for my named range---e.g., SalesOrderNumber---and hit the enter key.

Now, if I go to the Name Box and use the drop-down to select the named range "SalesOrderNumber", excel will select that range.

So in your case, do this for both Sales and Credit worksheets (ws)
0) Create a dynamic table around the data in both worksheets and name the tables SalesTable and CreditTable (no spaces)
1) Insert column to left of column C in both ws.
2) In the new column C, concatenate col A and B e.g., =CONCATENATE(B2,"/",C2) ...for both ws . You will need a separator because the date will pull through as a number when you concatenate, rather than a date. (see eg below)
01/01/2020
£125.20​
43831/125.2

3) Copy col C and paste as values into col C to create your concatenated data as a text string (for both ws)
4) Create the named ranges for both ws's e.g., ConcatSalesWS and ConcatCreditWS
5) Create a named range for Credit card fee column e.g., CardFee in the CreditTable
6) Insert column in your SalesTable for your index match formula

e.g.,
=INDEX(CardFee,MATCH(ConcatSalesWS,ConcatCreditWS,0))

I use named ranges and dynamic tables for all my index match formulas because
a) This way, I never miss data from my match, even if new data is added to a table.
b) The dynamic table fills formulas down automatically (useful if you use VBA as you only have to insert the formula in the top row)
c) You can refer to ranges via using headers and table names e.g., the credit fee column could be referred to as: ="CreditTable[Card Fee]" where Card Fee is the header. This simplfies matters tremendously when using VBA or formulas within a worksheet.

One word of warning though:
Index match formulas have a weakness when dealing with data that contains leading zeros or indeed if the data is entirely numerical but the data has somehow become stored as text: if you are matching two columns of numerical data, the format of those cells has to match e.g., convert all numbers 'stored as text' to numbers before doing the match. In your case, this shouldn't matter as you're using an alphanumeric concatenated string to match against.

Kind regards,

Doug.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

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