Forum Discussion
Dinamically Return Values From a Column Using A Merged Header Above It as Conditional
Hi, everyone!
I need some help with a formula.
See, I have the table layout as shown above and I need the "CS Month" (FM) column to return the values from daily CS+IN (Row 2) column dinamically if the date value on the merged header (Row 1) is the same as today. So basically, I want to show "today's CS+IN column in CS Month" dinamically. I've tried using indirect and match but I think I'm using it wrong since I couldn't make it work. How can I achieve such feature?
Additional Info:
1. I'm using dd/mm format over the merged headers on Row 1.
2. The date over the merged headers are stored in the first column of each section (Right above the "In" header in Row 2)
I thank you all in advance!
6 Replies
An advantage of using 365 is that array shaping functions are available to use as part of the calculation. For example the following generates an array containing the CS+IN columns only, each with the appropriate date header.
= LET( dateHdr, TAKE(WRAPCOLS(date, 5),1), wrapped, WRAPROWS(TOCOL(stock),5), CS, WRAPROWS(TAKE(wrapped,, -1), COUNT(date)), XLOOKUP(csMonth, dateHdr, CS) )From there, lookups should be simple. Whilst developing the formula the intermediate variables can be output to scratch space as helper ranges but, once everything is up and going, these can be removed.
- Hello Calheiro
FIRST THING First: are you sure 100% no 110% that the offsets are working properly? I suggest you dissect the formula and make sure each part is yielding the correct anticipated result and then see which part maybe failing.
By dissect I mean if you have something like say this
=IF(XLOOKUP(Lookup_Value,Lookup_Array,Return_Array,"-----",FALSE)="-----","Not Found","Found) then you will copy the XLOOKUP portion and see what it returns and by doing this you can see what part of the formula is misbehaving and giving you unexpected results.
Let us know how you progress.- Hello, Georgie! Thanks for replying.
So, I checked and my Match is returning the relative position of the section wich the merged header equals to today's date. Tbh, I can't seem to think how can I work from here though.
