Forum Discussion
Vlookup to find multiple variations of a text string
A formula solution. The key element is to perform a wildcard search for anything that contains a shift name, e.g. search for "*LMP*". There is a catch that the abbreviations for Early and Late are to be found within other shift codes so I converted them to "E0" and "L0" to avoid 'false positives'.
= IFERROR( INDEX( Name, MATCH( shift, INDEX(shiftTable, 0, day), 0 ) ), "" )
The names 'day' and 'shift' are relative references and the innermost INDEX returns the 3 rows for a given day of the table.
As for your other line of exploration, Power Query is available as an Office 2010 add-in free of charge from Microsoft. Your IT department could install it for you and I would recommend that they evaluate it themselves!
Anyway I set up the named ranges for the constants adjusted for the real data and set it going. It correctly gives me the names for the shifts on Monday but just repeats that name for the rest of the week.
I've run the Evaluate Formula feature and as it steps through it everything seems to be going right until the last step when it reverts to the name it gave for Monday.
Any thoughts?
The catch might be that 'day' is a relative reference to the indices 1,...7 above the table. To get a fresh value for each column, the array formula is applied to a single column and then copied across to give a set of 7 array formulas rather than one single array formula.
- 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 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.
- Not sure I follow but cant understand why you fix on my example works but it wont work on the real data.
Was wondering if there is an issue with shifts in the source data that don't appear in the left hand column of sheet2?
There are about 50 core shifts and while these might appear in the source data I haven't yet been able to ensure that they all appear in the shift column on sheet 2.