vlookup formatting problem


P

pbuscio

Hi, i have a vlookup to a table that has both dates and percentages in. when i do a simple vlookup the data is fine but i cannot do conditional formatting. however, if i change the vlookup to this

=IF(VLOOKUP($E16,tblCurrent,5,FALSE)>1,VLOOKUP($E16,tblCurrent,5,FALSE)*1,VLOOKUP($E16,tblCurrent,5,FALSE))

the conditional formatting works but anything that is a date becomes a percentage. so 11/26/13 becomes 4160400%

i tried using the text function for the last part of the formula but it didn't work

any ideas?
 
Ad

Advertisements

M

MyVeryOwnSelf

i tried using the text function for the last part of the formula but
it didn't work

any ideas?

Maybe something like:
=TEXT(VLOOKUP(...),IF(VLOOKUP(...)>1,"Mmm d, yyyy","0.00%"))
That is, the IF() test determines the format_text parameter of the TEXT() function, and the result is a text value.

If that doesn't meet the need, it'd help to see more edetail about what you're trying to achieve. Apparently, values <=1 should appear as a percentage, while others should appear as a date. Is there something more to it than that?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top