ISNA or VLOOKUP (DATE) or VLOOKUP (NUMBER)

[girltoni]

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.

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

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

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

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

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

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

 

[jasonb75]

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]

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]

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.

=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

 

[girltoni]

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

Oh my goodness!! Genius!!! It works beautifully!!!! Thank you so much!!!!