MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Help with IF


Posted by Paul on November 26, 2001 6:49 AM

I have this forumala to check for a workorder number put in A2 to see if it is on sheet workorders A:A, this is copyed down for about 100 rows, it is slowing the calculation time down, is there a way to put and if statement in so if A2 does not have a number in it the formula will not run, thanks for your help. =IF(COUNTIF(WorkOrders!A:A,A2),INDEX(WorkOrders!F:F,MATCH(A2,WorkOrders!A:A,0)),"No Match")


Posted by Dan on November 26, 2001 7:14 AM


You could use Vlookup instead of the IF statement which will calculate faster. The only way to not run the formulas is to change 'Calculation' to 'Manual' from 'Automatic' but you will need to manually recalculate the sheet each time you enter a value in A2.

Posted by Aladin Akyurek on November 26, 2001 9:33 AM


=IF(COUNTIF(WorkOrders!A:A,A2),INDEX(WorkOrders!F:F,MATCH(A2,WorkOrders!A:A,0)),"No Match")

Paul --

=IF(ISNUMBER(A2),IF(COUNTIF(WorkOrders!A:A,A2),INDEX(WorkOrders!F:F,MATCH(A2,WorkOrders!A:A,0)),"No Match"),"")

seems to be what you are asking for.

Otherwise, please elaborate a bit.

Aladin

Posted by Paul on November 26, 2001 12:10 PM

Thanks