VLOOKUP on Dates

[MikeyZ]

I know when you do a Vlookup on a date , say 4/16/1, it's going to pull the first one it finds and nothing else.
Suppose I have any number of lines showing a specific date, is there a way I can pull the sequential info from the cells in that column?

 

[alansidman]

Here is a UDF that will do what you need.

http://www.excelfox.com/forum/showthread.php/4-Vlookup-to-Return-Multiple-Values?highlight=mlookup

 

[wideboydixon]

The common solution looks like this:

Book1
	A	B	C	D	E
1	Date	Value		Lookup Date:	01/01/2018
2	01/01/2018	a		Values:	a
3	02/01/2018	b			d
4	03/01/2018	c			g
5	01/01/2018	d
6	02/01/2018	e
7	03/01/2018	f
8	01/01/2018	g
Sheet1

Cell Formulas
Range	Formula
E2	{=IFERROR(INDEX($B$1:$B$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8)-ROW($A$1)+1),ROWS($E$2:$E2))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Copy formula in E2 down as necessary. Adjust cell references to suit your data.

WBD

 

[Special-K99]

Need to be a bit more specific.
VLOOKUP returns one value, no more.

Are you saying you have multiple rows showing the same date and wish to return all of them?

Usual answer is something like this

=IFERROR(INDEX(range1,SMALL(IF((range1=specificdate),ROW(range1)),ROW(A1))-(ROW(firstrowofrange)-1),column number),"")
Array formula, use Ctrl-Shift-Enter

Post best post some example data.
You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

Or upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

 

[MikeyZ]

True. I should be a bit more explicit.
I run the VLOOKUP fx down the column.
I will then only pick up the first hit it comes across and go no further.

I have several PO's due on 4/16/18.
I want to show in my daily returns those PO's that are due 4/16/18.

 

[markmzz]

True. I should be a bit more explicit.
I run the VLOOKUP fx down the column.
I will then only pick up the first hit it comes across and go no further.

I have several PO's due on 4/16/18.
I want to show in my daily returns those PO's that are due 4/16/18.

Hi!

Another way (with WBD's layout):

In E2 and copy down

=IFERROR(INDEX(B$2:B$8,MATCH(0,INDEX(COUNTIF(E$1:E1,B$2:B$8)+(A$2:A$8<>E$1),),0)),"")

Markmzz

 

[alansidman]

Have you tested the UDF I provided you in post #2 ??

 

[MikeyZ]

Alan, will try it shortly. buried at the moment.

 

[MikeyZ]

trying to figure out how to use this fx.
I don't understand the CountIF criteria..

I have my static date in $C$6.

My data is in another sheet named POLINES.
AJ is where I am going to pull the PO# from.
AL is where the due date is located that will match up with $C$6.

S

 

[markmzz]

trying to figure out how to use this fx.
I don't understand the CountIF criteria..

I have my static date in $C$6.

My data is in another sheet named POLINES.
AJ is where I am going to pull the PO# from.
AL is where the due date is located that will match up with $C$6.

S

Hi!

Try this in D6 of the Main sheet (the sheet where you have the static date - $C$6) and copy down

=IFERROR(INDEX(POLINES!$AJ$2:$AJ$8,
MATCH(0,INDEX(COUNTIF($D$5:$D5,POLINES!$AJ$2:$AJ$8)+(POLINES!$AL$2:$AL$8<>$C$6),),0)),"")

A

B

C

D

E

F

AI

AJ

AK

AL

AM

AN

1

Sheet

1

PO's

Date

Sheet

2

Main

2

PO01

01/04/2018

POLINES

3

3

PO02

02/04/2018

4

4

PO03

03/04/2018

5

Date

PO's

5

PO04

04/04/2018

6

01/04/2018

PO01

6

PO05

01/04/2018

7

PO05

7

PO06

02/04/2018

8

8

PO07

03/04/2018

9

9

10

10

11

11

12

12

***

**

**

**********

*******

**

********

**

***

**

*******

****

**********

****

********

<tbody>
</tbody>

By the way, how many rows your data have?

Markmzz