Lookup where v & hlookup just won't work!

Hi - thanks in advance to anyone who can help with this! It's taken me over a week off and on to fathom and still I am stuck!

If you love a challenge then this is for you!

Basically I have a list of employees who each have a 10 week work rota - some are the same as others but I have over 600 unique rotation plans between 6,000+ staff members

I have identified what their plan looks like (don't ask me how but I have and I don't want to go back and start again) - I have also identified what the plan they are on is to be called -

What I can't do is work out who is on which plan

In short...

I have a Rotation Plan name with a Rotation Plan description next to it
Employee numbers with Rotation Plan descriptions next to them
I need to get the Rotation Plan name next to the employee numbers

vlookup won't work because the description is too long I believe - it just won't let me do it

I've tried replacing common characters in the description with numbers so as to create a number and use lookup that way but that won't work either - it gives the same number to different descriptions!

I hope this is clear

Attached are the two tabs which I need to relate to eachother - with the Rotation Plan Name in the the PLAN tab next to the Employee Number in the EMPLOYEE tab

Fire away if not clear

Thanks again - Paul

The problem is that Excel will only look at the first 254 characters and you have about 330 there. I took a crack at it using only the first 254 characters. (see attachment). You're going to have to come up with some way of reducing the # (maybe doing a search/replace and removing all periods?). I'm not sure but I believe you'd run into the same problem with VBA methods.

Does this formula work?

=INDEX(Plan!$B$3:$WY$3,SUMPRODUCT((Plan!$B$3:$WY$6=Employee!A4)*(COLUMN(Plan!$B$3:$WY$6)-COLUMN(Plan!$B$3)+1)))

copied down

Hi Paul,

The standard approach would normally be to wrap a MATCH() function inside an INDEX() function. i.e.

Please Login or Register  to view this content.

However I suspect this is not working because (most of ) your plan descriptions are in excess of 300 characters and I guess Excel doesn't like this.

I note that many of the strings are the same at various lengths. For instance at the first 10 characters level, 5793 of the 6326 strings are identical. It may be possible to break these down in some way to overcome what appears to probably be a 256 character restriction.

If you can give us some idea of the algorithm for producing the plan descriptors it may be possible to do something further.

Regards

Hi Wilgoss,

If you use the leftmost 250 characters of your Rota string things seem to work correctly. There is only 1 duplicate if you truncate the rota string at 250.

I first deleted duplicates in all rota and came up with a unique list. Then put Plan # next to it to use as a vlookup table. Then one formula was almost good enough except for the strings were too long. Trim to look at only first 250 characters and poof, it worked.

See the attached for your answer.

id just bulk replace bbw with say x
that would drastically reduce string length then repeat with some other string until its reduced to acceptable lengths

It seems those items have 2 occurances in the Plan sheet (column RV and RY) !?? Is that correct?

Also, if the items are all in 1 row in the Plan sheet, then you can update formula to a slightly more efficient...

=INDEX(Plan!$B$3:$WY$3,SUMPRODUCT((Plan!$B$4:$WY$4=Employee!A4)*(COLUMN(Plan!$B$4:$WY$4)-COLUMN(Plan!$B$4)+1)))

If you want the first match, try this instead:

=INDEX(Plan!$B$3:$WY$3,MIN(IF(Plan!$B$4:$WY$4=A4,COLUMN(Plan!$B$4:$WY$4)-COLUMN(Plan!$B$4)+1)))

confirmed with CTRL+SHIFT+ENTER, not just ENTER and copied down.