SOLVED

Vlookup to find multiple variations of a text string

Copper Contributor

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 for straightforward shifts but occasionally we have to ask staff to double up on shifts and the vlookup formula I am using wont find the double shifts.

The problem is that there are too many possible combinations of double shifts for me to list them all on the worksheet so I need a formula that can find any possible combination and display the staff member who has been allocated it?

Sample File attached.

 

Thanks

28 Replies
Apologies lol. I'm in the UK so was quite late when I read your message. Read it again this morning and made perfect sense. Working perfectly now.
I'm going to need to change some of the shift names as you did with the L & E shift and am checking with my managers if that will have any implications?
One thing I thought of was that since all double shifts have a + between them could we not change the names range "shift" to look for that as a wildcard? I tried "*+"&range&"+*" but that didnt work.
Thanks again for your help.

@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.

@Detlef Lewin 

I liked the reference to the Bill Jelen podcast.  I have yet to get around to using measures and DAX so perhaps this will encourage me to seek out new problems where I can use it!

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!

@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!

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.