Help with an index match formula involving returning Dates

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
718
Office Version
  1. 365
Platform
  1. Windows
I have the below formula that indexes column Q. Column Q has dates in it as well as blanks. This formula is working for me with one exception:

If there are blank cells in Q the formula will return the date: 01/00/1900

How can I modify this formula to return a blank if there is a blank in column Q?

=IFERROR(TEXT(INDEX('Manual Log'!Q:Q,MATCH('Parts in Engineering'!A2,'Manual Log'!A:A,FALSE)),"MM/DD/YYYY")&"","")

Any help would be appreciated.

Thank you

Carla
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
How about
=IFERROR(TEXT(IFERROR(1/(1/INDEX('Manual Log'!Q:Q,MATCH(A2,'Manual Log'!A:A,FALSE))),""),"MM/DD/YYYY")&"","")
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,637
Office Version
  1. 365
Platform
  1. Windows
Try

=IFERROR(TEXT(INDEX('Manual Log'!Q:Q,MATCH('Parts in Engineering'!A2,'Manual Log'!A:A,FALSE)),"MM/DD/YYYY;;;"),"")
 

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
718
Office Version
  1. 365
Platform
  1. Windows
Works perfect, thank you Fluff! :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You're welcome & thanks for the feedback
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,637
Office Version
  1. 365
Platform
  1. Windows
If Manual Log'!A:A contains only unique entries, then you could also use

=TEXT(MAXIFS(Manual Log'!A:A,A2,Manual Log'!Q:Q),"mm/dd/yyyy;;;")

The semi-colons in the text format eliminate the 01/00/1900 dates and the removal of the match function does away with the need for iferror.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,637
Office Version
  1. 365
Platform
  1. Windows
Too late to edit my previous reply but I just realised that I used the syntax for sumif in error, the formula should be

=TEXT(MAXIFS(Manual Log'!Q:Q,Manual Log'!A:A,A2),"mm/dd/yyyy;;;")
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
=IF(ISNUMBER(V(INDEX('Manual Log'!Q:Q,MATCH('Parts in Engineering'!A2,'Manual Log'!A:A,0)))),TEXT(V(),"MM/DD/YYYY"),"")


For the foregoing formula to work, we need to install the V() function using Alt+F11 of which the code is as follows:

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen
Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function
 

Watch MrExcel Video

Forum statistics

Threads
1,129,680
Messages
5,637,749
Members
416,982
Latest member
lisam77

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
Top