Forum Discussion
Solved
Vlookup to find multiple variations of a text string
Hi, first time post here so bear with me! I am trying to create a worksheet that displays a list of shift types with names of staff allocated that shift over the period of a week. I can do this ...
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.
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!
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!