Thread: If Problem (Excel 2000)

1. If Problem (Excel 2000)

Hi

I am trying to say here if anywhere within the range P7:P90 in worksheet Lead Range contains the text "Too Low","Needs Check","OK"

Too Low may appear more than once, Should only be OK if it does not appear at all

=IF('Lead Range'!P7:P90="Too Low","Needs Check","OK")

Many thanks

2. Re: If Problem (Excel 2000)

Try this:

=IF(ISNA(MATCH("Too Low",P7:P90,0)),"OK","Needs Check")

The MATCH function tries to find the text "Too Low" in the range P7:P90. If not found, it returns #N/A, so we use ISNA to test for this.

3. Re: If Problem (Excel 2000)

Hi Hans

I can't get it to work, does it matter that the text Too Low is created by a formula ie =IF(ISBLANK(Q7),"",IF(Q7<O7,"Too Low",IF((AND(Q7>=O7,Q7<M7)),"Needs Approval","OK")))

Many Thanks

4. Re: If Problem (Excel 2000)

Hi Hans

I think the problem is the formula does not contain the sheet ref Lead Range this is where the range is situated,

Many Thanks

5. Re: If Problem (Excel 2000)

It should work, but I forgot to specify the worksheet:

=IF(ISNA(MATCH("Too Low",'Lead Range'!P7:P90,0)),"OK","Needs Check")

6. Re: If Problem (Excel 2000)

Hi Hans

Works fine now, thanks again.