Discussion
I consider myself above average with Excel, but I can't seem to crack this 'issue.'
We make frequent collections from a large warehouse and they have a complex booking in process. They require a haulier to find the date they are collecting, move along a row and establish which crew is on night shift or day shift, and then find the corresponding contact details for the relevant shift.
I would like it so that a haulier can put a date in, and it spits out the relevant information. I thought originally a vlookup table could work, but it is looking for a date in a table, then needs to report what column number either a 'D' or 'N' falls in.
Any help much appreciated!

We make frequent collections from a large warehouse and they have a complex booking in process. They require a haulier to find the date they are collecting, move along a row and establish which crew is on night shift or day shift, and then find the corresponding contact details for the relevant shift.
I would like it so that a haulier can put a date in, and it spits out the relevant information. I thought originally a vlookup table could work, but it is looking for a date in a table, then needs to report what column number either a 'D' or 'N' falls in.
Any help much appreciated!
A horrid combination of MATCH and INDIRECT functions could work:
E.g -- Look up the Date and then in that row find a "D". From there you know the column and so can assume Crew 1 is in Column 1
Then repeat the formula for "N" to look up the night crew
="Crew " & MATCH("D",INDIRECT("B" & MATCH(H4,B3:B10,0)+2 & ":F"& MATCH(H4,B3:B10,0)+2),0)
If I get more time, I'll try and see if it works.
E.g -- Look up the Date and then in that row find a "D". From there you know the column and so can assume Crew 1 is in Column 1
Then repeat the formula for "N" to look up the night crew
="Crew " & MATCH("D",INDIRECT("B" & MATCH(H4,B3:B10,0)+2 & ":F"& MATCH(H4,B3:B10,0)+2),0)
If I get more time, I'll try and see if it works.
nickd01 said:
A horrid combination of MATCH and INDIRECT functions could work:
E.g -- Look up the Date and then in that row find a "D". From there you know the column and so can assume Crew 1 is in Column 1
Then repeat the formula for "N" to look up the night crew
="Crew " & MATCH("D",INDIRECT("B" & MATCH(H4,B3:B10,0)+2 & ":F"& MATCH(H4,B3:B10,0)+2),0)
If I get more time, I'll try and see if it works.
Cheers. I think I have sussed it. It isn't clean, involves IFS functions and a lot of conditional formatting, but it now shows the relevant Crews for Day / Night shift depending on a set date.E.g -- Look up the Date and then in that row find a "D". From there you know the column and so can assume Crew 1 is in Column 1
Then repeat the formula for "N" to look up the night crew
="Crew " & MATCH("D",INDIRECT("B" & MATCH(H4,B3:B10,0)+2 & ":F"& MATCH(H4,B3:B10,0)+2),0)
If I get more time, I'll try and see if it works.
Now to have it tested in anger!
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff


