# Return date based on 2 criteria

#### dunmore83

##### Well-known Member
A B C D
2 26/11/2013 144
10 03/09/2013 345
144 438
345 04/10/2013 1168
438 05/10/2013 1442
500 1528
550 25/11/2013 1611
1442 1612
1528 25/10/2013 1617
1600 25/09/2013 1629

In column D I need the date that lines up with the same number in column A. For example if 345 from column C is found in column A then return the date in column B which is 04/10/2013 & this will be place in D2; otherwise return 0 (i.e. if the search was for 1442 result would be 0)

THANKS!

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### Oeldere

##### Well-known Member
why not just filter in column A on the criteria 345.

#### Andrew Poulsom

##### MrExcel MVP
Formula in D2 copied down:

Excel 2010
ABCD
1ABCD
2226/11/20131440
31003/09/201334504/10/2013
414443805/10/2013
534504/10/20131168
643805/10/201314420
7500152825/10/2013
855025/11/20131611
914421612
10152825/10/20131617
11160025/09/20131629
Sheet1
Cell Formulas
RangeFormula
D2=IFERROR(VLOOKUP(C2,A\$2:B\$11,2,FALSE),"")

The cells in column D have the number format:

dd/mm/yyyy;;0

##### MrExcel MVP
A2:C11 houses the sample.

D2, copied down:

=IFERROR(VLOOKUP(\$C2,A:B,2,0),"")

This returns a blank, not a zero on purpose. Custom format the result range in D as date.

#### dunmore83

##### Well-known Member
Thanks. Almost works. However if the lookup number is not found it returns 00/01/1900 ??

#### Andrew Poulsom

##### MrExcel MVP
Did you see what I wrote?

The cells in column D have the number format:

dd/mm/yyyy;;0

#### dunmore83

##### Well-known Member
Yep I have formatted column D with dd/mm/yyyy;0 but it is still returning 00/01/1900 for those vlookup vales not found. For those vlookup values found but the adjacent cell has a blank date then it is working & returns a 0 or a blank (depending on formula)

#### Andrew Poulsom

##### MrExcel MVP
It's:

dd/mm/yyyy;;0

Note the two semicolons.

##### MrExcel MVP
Yep I have formatted column D with dd/mm/yyyy;0 but it is still returning 00/01/1900 for those vlookup vales not found. For those vlookup values found but the adjacent cell has a blank date then it is working & returns a 0 or a blank (depending on formula)

D2, copied down:

=IFERROR(1/(1/VLOOKUP(\$C2,A:B,2,0)),"")

#### dunmore83

##### Well-known Member
ok now it works !! thanks

Replies
5
Views
537
Replies
3
Views
371
Replies
5
Views
890
Replies
8
Views
184
Replies
0
Views
350

1,195,598
Messages
6,010,642
Members
441,558
Latest member
lambierules

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back