Return date based on 2 criteria

D

dunmore83

Well-known Member
Joined
Aug 24, 2011
Messages
540
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)

Can someone please help with this problem?

THANKS!
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
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
 
Upvote 0
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.
 
Upvote 0
Thanks. Almost works. However if the lookup number is not found it returns 00/01/1900 ??
 
Upvote 0
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)
 
Upvote 0
dunmore83 said:
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)
Click to expand...

D2, copied down:

=IFERROR(1/(1/VLOOKUP($C2,A:B,2,0)),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
HELP IDENTIFY ORIGINAL START DATE BASED ON CONTINOUS SERVICE RULES
Replies
0
Views
339
mrshuck
M
SaraWitch
  • Solved
Return first Monday in April from one date and first Monday in current year April if another is blank
2 3
Replies
28
Views
794
Dave Patton
D
C
  • Solved
Extract Dates from 2 columns
Replies
2
Views
300
Peter_SSs
Peter_SSs
H
  • Solved
Creation of arrows for 2 family trees.
Replies
8
Views
176
harzer
H
M
  • Question
Counting specific dates from multiple columns
Replies
2
Views
130
mattbird
M

Forum statistics

Threads
1,214,636
Messages
6,120,666
Members
448,977
Latest member
moonlight6

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top