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
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,558
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
11,558
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

Watch MrExcel Video

Forum statistics

Threads
1,118,609
Messages
5,573,207
Members
412,514
Latest member
LangdonJohn
Top