vlookup with dates?

[Ian1976]

U	V	W	X	Y	Z	AA
6-Nov-18	7-Nov-18	8-Nov-18	9-Nov-18	10-Nov-18	11-Nov-8	12-Nov-18

<tbody>
</tbody>

Hi all,

not sure its possible but I have "A" column with serial numbers in 123456 and column "O" with dates in, what I'm trying to do is when I sort them in to date order I'd like it to populate a table like this...If the date in column O matches the date on the table in column U then paste in the related serial number, also any suggestion on what I can do if I have 2 dates the same?

Thanks
Ian

Column A Column O
123456 6-Nov-18
654321 8-Nov-18
456123 10-Nov-18

 

[RasGhul]

You could try this workday formula in column P, for me this found the next workday after each date in column O.

=WORKDAY(O2,1)

Then edit the array formula to look at the P column;

=IFERROR(INDEX($A$2:$A$11,SMALL(IF($P$2:$P$11=U$1,ROW($P$2:$P$11)-ROW($P$2)+1),ROWS(U$2:U2))),"")

This is now returning Serial numbers in workday dates only.

 

[Ian1976]

How would that work as I still need some of the items to be used on the weekends? Thanks

 

[Ian1976]

How would that work as I still need some of the items to be used on the weekends? Thanks

Sorry missed your last post! I’ll have a play with it, thanks!

 

[Ian1976]

Hi RasGhul,

I've had a look and that won't work for me? Sometimes when a serial drops on to a weekend I don't use it meaning I can move it out a day or two? not sure if that's to many queries'?

Thanks Ian

 

[RasGhul]

Ok if we go back to your original formula and just add a look at column P you can just enter a number that will bump forward to a day that suits;

[COLOR=#333333]=IF(I2=0,"",ROUNDDOWN(S2+((D2+800)/I2),0))[/COLOR][COLOR=#ff0000]+P2[/COLOR]

Then your dates can stay in Column O, and use the original array;

=IFERROR(INDEX($A$2:$A$11,SMALL(IF($O$2:$O$11=U$1,ROW($O$2:$O$11)-ROW($O$2)+1),ROWS(U$2:U2))),"")

You could also highlight all weekend dates in Column O with Conditional Formatting.

Highlight Column O

Conditional Format, New Rule, Use a Formula to Determine which cells to Format

Then enter =OR(WEEKDAY($O1)=7,WEEKDAY($O1)=1) choose a color for fill or change text color

 

[Ian1976]

Hi RasGhul

Is there a way to make this statement indefinite?

=D2-IF(Q2=1,I2,IF(Q2=2,I2*2,IF(Q2=3,I2*3)))

Thanks Ian

 

[RasGhul]

I'm not sure what you need the end result to be...

So in each test did you want the 1st AND the 2nd 2 to increase by 1 per row in IF(Q2=2,I2*2?

I'll probably need to see some more sample data so I can see what you need to achieve.

 

[Ian1976]

Sorry, D2 is a number which could be anything from 16000 to -1000, Q2 is a number between -5 and 5, and I2 is another 3 digit number ie 351!

Does that help? ??

 

[RasGhul]

Ok so when you change only this row you want all of the rows to change accordingly?

 

[Ian1976]

No I only want the cell with this formula in to change! ??