Forum Discussion

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Apr 28, 2019

othafa 

othafa wrote:
 I tried "*+"&range&"+*" but that didn't work.

I think you were within a whisker of getting it to work though.  If you pad both the lookup value and the lookup array with leading and trailing "+"s, you will find the strings you are searching for but "+L+" is not present in "+LMP+".  You will also locate it successfully within "+L+N+", just as you want.

 

p.s. I seem to have had different versions of the workbook.  The attached uses a relative reference within the formula for 'shift' which allows the worksheet formulas to be entered without CSE.

othafa's avatar
othafa
Copper Contributor
Apr 29, 2019
Sorry you've lost me now. The sheet you sent in your last message has this formula in each cell of the table on sheet 2 {=IFERROR( Name, MATCH( shift, "+"&INDEX(shiftTable, 0, day)&"+", 0 ) ), "")} which looks like an array and which I can only replicate by using CSE which you say in your last paragraph is not necessary. I have amended the shift formula so it's the same as that in the sheet you attached?

When I do that however it now gives me the correct name in the first row but then replicates those names for the remaining rows.

Sorry to still be struggling with this!
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Apr 29, 2019

    othafa 

    Oops! My fault.

    I clearly changed the worksheet but uploaded it before saving!

    The version I am now about to post picks up both the day and the shift code by relative referencing.  Each cell requires a reference to be made to the 'shiftTable' but padded with "+".  If this 'padding' were done in a worksheet formula this would require CSE but by hiding it away in the Named Formula the array operation happens by default.

    I apologise for changing strategy mid-stream; it is not a good teaching style!

    • othafa's avatar
      othafa
      Copper Contributor
      Apr 29, 2019
      Ok, that works now. Have made the changes and will let managers loose on it later. I ran various tests and couldn't break it but my managers have a knack for finding anomalies!

      Thank you so much for your help, hopefully you've heard the last from me.

Resources