Formula to ignore blanks and no numeric data

[ungers]

Hi,

I have the following as part of an IF formula, I would like it to ignore any non-numeric data that may have ended up in the range but can't seem to get it to work.

=IF(LOOKUP((INDEX(FreqRange,MATCH(MIN(ABS(FreqRange-(INDIRECT("'"&Background!$L$1&"'!"&CELL("address",A2))))),ABS(FreqRange-(INDIRECT("'"&Background!$L$1&"'!"&CELL("address",A2)))),0))),FreqRange,ValueRange)<=RESULTS!$U$35

Functionality is to take the value from a user selected sheet INDIRECT("'"&Background!$L$1&"'!"&CELL("address",A2))), find the closest match from range FreqRange, pull out the corresponding value from ValueRange, and then the IF function is true if the value is <= the value in cell RESULTS!$U$35.

Function-wise all performs as it's supposed to, but i'd like to be able to ignore any non-numeric data in FreqRange?


Any help would be greatly appreciated?!

Many thanks,

D

[Vikas_Gautam]

Need More information
Provide a sample workbook with expected result...


Don't forget to click *

[davehow87]

Hi,

Can anyone help me with this problem please.

I have this IF formula that works but I want to add an extra condition and can't get it to work- just says VALUE# in the cell.

The current formula is---

=IF(GL5="GREENKEEPER","REQUIRED",IF(GL5="GOLF ATTENDANT","REQUIRED",IF(GL5="ASSIST MAN GOLF","REQUIRED",IF(GL5="GOLF MANAGER","REQUIRED",IF(GL5=GL110,"INPUT JOB")))))

and that works but in need something like this---

=IF(GL5="GREENKEEPER"+A15="YES","REQUIRED",IF(GL5="GOLF ATTENDANT","REQUIRED",IF(GL5="ASSIST MAN GOLF","REQUIRED",IF(GL5="GOLF MANAGER","REQUIRED",IF(GL5=GL110,"INPUT JOB")))))

BUT THE + A15="YES" PART WON'T WORK- is it possible to have 2 arguments like this??

thanks

[martindwilson]

davehow87
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[ungers]

Managed to achieve what I needed using to =IF(ISNUMBER... in a seperate column to pull in the values, worked for me as I could do some additional formatting and sorting on the data at the same time and then keep this in the background.