• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

INDEX MATCH Help please

Taryn

New Member
Hi,
I'm not great at INDEX MATCH but think this is what I need, basically I am trying to get the same results that I already have in the yellow highlighted section but instead of having to manually change the formula to update to the next month each time I want it to do this automatically.
I have a different sheet in my actual file that has input information and the month is on there I just forgot to add it in the sample but you can just link to a cell anywhere on this file and I can update it to the correct one.
Help will be greatly appreciated
 

Attachments

  • CHANDOO.xlsx
    24 KB · Views: 5
In the attached, there's a formula in cell R24 (copied down) which gives the same results you have in cell P24 and below.
That formula refers to cell P22 containing the manually input 31st May 2023, but it could contain a formula such as =EOMONTH(TODAY(),0) which today would return the 30th June 2023, or =EOMONTH(TODAY(),-1) to give 31st May 2023. This would make the sheet dynamic to the current date. Up to you. Naturally, the cell P22 can be anywhere more convenient.
I haven't yet added the second column because these formulae could be easier if I know:
1. which version of Excel you're using? (Does it have the LET function available on the sheet? What about LAMBDA? Also XLOOKUP?)
2. you have a formula in cell C3 (which refers to a cell I have no access to) that results in a date the 1st of the month. All the rest of the dates refer to the end of the month (more in line with accounting conventions). To make it more robust, I suggest that cell C3 formula be tweaked possibly by changing the:
Summary.xlsb]INPUT'!D5,-1)+1
to:
Summary.xlsb]INPUT'!D5,0)
This would hopefully bring that date to the end of January. but I can't test.
Can you do that and reattach?
 

Attachments

  • CHANDOO53624.xlsx
    24.8 KB · Views: 2
Last edited:
Thank you for the formula for the first column, it looks quite involved!

  1. It looks like I have all those functions available to me me but I have never used them so have no idea what they do
  2. The formula in the INPUT sheet that C3 is looking up is a TODAY(),-1
  3. The reason I use EOM -1)+1 is because in other places dates are written as mmm-yy formats and that always defaults to the 1st day of the month, if I change to what you suggest it will always be the last day of the month so when I need to do lookups it won't match.
 
The formula in the INPUT sheet that C3 is looking up is a TODAY(),-1
Eh?!
What is in cell [New Rec Summary.xlsb]INPUT'!D5 ?
It can't be today(),-1 to come back with 1st January!

The reason I use EOM -1)+1 is because in other places dates are written as mmm-yy formats and that always defaults to the 1st day of the month, if I change to what you suggest it will always be the last day of the month so when I need to do lookups it won't match.
OK, then it would be good to make all dates consistently the 1st of the month (or the last day of the month (more in line with accounting practices)), because at the moment, row 3 contains inconsistent dates (only the leftmost one) and our lookups (matches) won't be robust/consistent.

Could you re-attach a file with all this corrected? (Worst case, leave the formula in C3 as just as a plain link:
='https://wtwonlineuk-my.sharepoint.c...ler_willistowerswatson_com/Documents/Desktop/[New Rec Summary.xlsb]INPUT'!D5
, refresh/recalculate, save, re-attach, then I'll know what's in that cell)
 
Sorry, I was thinking of a different cell with the today formula! [New Rec Summary.xlsb]INPUT'!D5 Is just a hard coded 01/01/2023

Reattached
 

Attachments

  • CHANDOO.xlsx
    25 KB · Views: 3
I've got things to do now so present work in progress, and I can ask a question.
In the attached, in the cells S23:V27 there's some formulae:
Ignore column S, just there for developmental reasons.
Cell T24 has a formula: =MonthCatValue($C$23:$N$37,$A$23:$B$37,$B24,$P$22) which extracts a single value from a table.
84322
Q1. I've called it MonthCatValue but it may not be the best name; It uses:
  • the table of values including date headers, dubbed myData,
  • what I've called Categories in cell B23:B37, [note for the future, that you could use column A instead of column B if the names are exactly the same in both tables],
  • the category to look up (ThisCat)
  • the end of month date in cell P22 (dubbed EOCurrMnth).
What would be better names for the blue items above?

I'm intending to use this MonthCatValue formula within other bespoke functions to get you the two columns of data you need in a simple way.
 

Attachments

  • CHANDOO53624.xlsx
    26.7 KB · Views: 3
Last edited:
Back
Top