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]

Hi Ian,

Enter this as below, then drag across your dates then down as far as what you need;

Book1
	A	O
1	Serial	Dates
2	123456	6/11/2018
3	123457	6/11/2018
4	123458	8/11/2018
5	123459	9/11/2018
6	123460	10/11/2018
7	123461	10/11/2018
8	123462	8/11/2018
9	123463	7/11/2018
10	123464	12/11/2018
11	123465	12/11/2018
Sheet1

Book1
	U	V	W	X	Y	Z	AA
1	06-Nov-18	07-Nov-18	08-Nov-18	09-Nov-18	10-Nov-18	11-Nov-18	12-Nov-18
2	123456	123463	123458	123459	123460		123464
3	123457		123462		123461		123465
Sheet1

Cell Formulas
Range	Formula
U2	{=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))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Ian1976]

Hi RasGhul,

That is just what i'm after, and works perfectly, although - the column "O" has formula in it not a standard date! this is the formula i'm using - =IF(I2=0,"",(S2+((D2+800)/I2)))

Is there a way to get around this? Appreciate your help!

Ian

 

[RasGhul]

Ok Ian,

What is the resulting value from your IF formula, if the value is a valid date then the formula should still work.

Can you paste what the output of =IF(I2=0,"",(S2+((D2+800)/I2))) is?

 

[Ian1976]

Morning RasGhul,

Column "O" is formatted as a "Date - 14/03/2012"

Cell I2 = "574" "General"
Cell S2 = "=Today()"
Cell D2 = Random number been "-1000:16000"

Thanks!

 

[RasGhul]

Hi Ian,

The actual number that excel see's behind =IF(I2=0,"",(S2+((D2+800)/I2))) is e.g 43448.8741258741 which is not the same as 14-12-18 if we had that in the Dates header column.

We can get rid of all the decimal places with =IF(I2=0,"",ROUNDDOWN(S2+((D2+800)/I2),0)) which becomes e.g 43448 excel date serial number for 14-12-18.

**Note that your dates header (U1-AB etc) needs to be wide enough to match the random dates in the O Column.

 

[Ian1976]

Hi RasGhul,

That works perfectly mate, thank you son much for your help! ?

 

[RasGhul]

You're welcome.

 

[Ian1976]

Hi RasGhul,

Sheets working great, but i’d Like to make a modification if it’s possible? Is there anyway I could insert a column somewhere that when it is a weekend I can enter “0” against that serial number so info it falls on a weekend date it carries it over to the next week day?
? thanks

 

[RasGhul]

Hi Ian,

It may be easier to have your Dates header as workday dates only?