ISNA or VLOOKUP (DATE) or VLOOKUP (NUMBER)

girltoni

New Member
Joined
Oct 15, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hello,

I am doing a vlookup on one column's values which includes multiple data types (dates, number, or #N/A for not available.
The one column's results will either be:
1) a date (formatted MM/DD/YYYY)
2) or it will be a number, positive or negative
3) or it will be not available (#N/A). (as it not included on the list)
4) Lastly, if it is blank, no date or no number - the dates are coming in as"1/0/1900". I would rather these just stay blank instead. So blank for #N/A and blank for blanks would be the desired result for both of these.
1603906981019.png


Tried several ways to go about this...

This resulted in the my #N/As being blank like I wanted and the dates being formatted correctly; however, the number do not show - it is giving me a #VALUE error. Blanks are
1603907202652.png

=IF(ISNA(VLOOKUP(J166,'IOPT PC'!$G:$X,18,FALSE))," ",TEXT(VLOOKUP([@NEWFPC],'IOPT PC'!$G:$X,18,FALSE),"MM/DD/YYYY"))

1603906523767.png


This one gave me my dates and my numbers as I needed them formatted. However, my #N/A results are still showing #N/A and I am wanting them to be blank. Blanks are
1603907215328.png


=(IFERROR(TEXT(VLOOKUP([@NEWFPC],'IOPT PC'!$G:$X,18,FALSE),"MM/DD/YYYY"),(TEXT(VLOOKUP([@NEWFPC],'IOPT PC'!$G:$X,18,FALSE),0))))
1603906614115.png

This one gave me the dates as dates, the #N/As as blanks, but made my numbers ######## Blanks are
1603907224113.png


=IFERROR(INDEX('IOPT PC'!$X$1:$X$10000,MATCH(J166,'IOPT PC'!$G$1:$G$10000,0)),"")

1603906733004.png
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,515
Office Version
  1. 365
Platform
  1. Windows
A formula cannot identify the difference between a date and a number (dates are just numbers formatted differently excel counts the days from 1/1/1900 to get the current date).

If the dates are always current and the values are less than say 40000 then it should be possible, but numbers in the range of 44000 to 45000 will conflict with current dates.
 

girltoni

New Member
Joined
Oct 15, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Yes days will always be way less than 40000. Dates current??? The dates will always be within a couple months in the past to a couple months in the future... No more than 24 months either way most likely. Hopefully this helps!
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,515
Office Version
  1. 365
Platform
  1. Windows
Yes days will always be way less than 40000.
I asked about values, not days. Negative numbers will always show as #### when formatted as date. Positive values and dates are effectively the same thing.

Excel Formula:
=IFERROR(1/(1/INDEX('IOPT PC'!$X$1:$X$10000,MATCH(J166,'IOPT PC'!$G$1:$G$10000,0))),"")
Apply the following custom format to the cells with the formula.

[>40000]mm/dd/yyyy;General
 
Solution

Forum statistics

Threads
1,147,737
Messages
5,742,896
Members
423,760
Latest member
photogfrog

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