If Match Roundup .

[Sunline]

Hello all , i have this function here
=IF(ISTEXT(MATCH(DO2,$FC$2:$FC$38,0)),IF(AND(EG2>=0,EG2<=10),21-ROUNDUP(EG2/0.5,0),0),0)
Im wanting to know if and how many times i can extend this function using 2007 , in other words im wanting to add more too it .
Im wanting to match more cols , would like too match also cols DP DQ DR , which piece of this function do i cut and paste to make it work , had no luck so far . Thanks .

 

[barry houdini]

Hello Sunline,

I'm not quite sure what you are trying to do, can you describe in words. Because MATCH function will only ever return an error or a number it doesn't really make sense to use this part

ISTEXT(MATCH(DO2,$FC$2:$FC$38,0))

That can only ever return FALSE......

 

[devonknows]

Hello all , i have this function here
=IF(ISTEXT(MATCH(DO2,$FC$2:$FC$38,0)),IF(AND(EG2>=0,EG2<=10),21-ROUNDUP(EG2/0.5,0),0),0)
Im wanting to know if and how many times i can extend this function using 2007 , in other words im wanting to add more too it .
Im wanting to match more cols , would like too match also cols DP DQ DR , which piece of this function do i cut and paste to make it work , had no luck so far . Thanks .

From what i can gather to your question, you can use as many if statements as you wish.

=IF(CRITERIA, TRUE, IF(CRITERIA, TRUE, IF(CRITERIA, TRUE, IF(CRITERIA, TRUE, IF(CRITERIA, TRUE, IF(CRITERIA, TRUE, FALSE))))))
(Always ensure you supply enough closing brackets, for each if statement you must add a closing bracket if used in the above method.

i have a formula on a sheet that consists of approximately 30 IF Statements combined. To use multiple MATCH you can use (If used above replace criteria with the below etc.)

=IF(MATCH(OR(MATCH(DO2,$FC$2:$FC$38,0),MATCH(DO2,$FC$2:$FC$38,0),MATCH(DO2,$FC$2:$FC$38,0)),TRUE,FALSE)

As i say though thats all i can derive from the information you given as requested by the other reply can yousupply more data

Regards

 

[Sunline]

Hi , yes sorry for that , i was experimenting , actual function is .
=IF(ISNUMBER(MATCH(DO2,$FC$2:$FC$38,0)),IF(AND(EG2>=0,EG2<=10),21-ROUNDUP(EG2/0.5,0),0),0)
Im wanting to match another col or more cols with numbers , so as well as Match col DO2 also DP DQ DR .
Wanting to match text as well so im not sure how this function may look . God i hope this makes sence . Thanks Barry .

 

[devonknows]

Hi , yes sorry for that , i was experimenting , actual function is .
=IF(ISNUMBER(MATCH(DO2,$FC$2:$FC$38,0)),IF(AND(EG2>=0,EG2<=10),21-ROUNDUP(EG2/0.5,0),0),0)
Im wanting to match another col or more cols with numbers , so as well as Match col DO2 also DP DQ DR .
Wanting to match text as well so im not sure how this function may look . God i hope this makes sence . Thanks Barry .

The formula should look something like this.

=IF(ISNUMBER(MATCH(DO2,$FC$2:$FC$38,0)),IF(AND(EG2>=0,EG2<=10),21-ROUNDUP(EG2/0.5,0),0),IF(ISNUMBER(MATCH(DP2,$FC$2:$FC$38,0)),IF(AND(EG2>=0,EG2<=10),21-ROUNDUP(EG2/0.5,0),0),IF(ISNUMBER(MATCH(DQ2,$FC$2:$FC$38,0)),IF(AND(EG2>=0,EG2<=10),21-ROUNDUP(EG2/0.5,0),0),IF(ISNUMBER(MATCH(DR2,$FC$2:$FC$38,0)),IF(AND(EG2>=0,EG2<=10),21-ROUNDUP(EG2/0.5,0),0),0))))

Providing that you are using the same roundup formula for each column so this will check if column DO then DP, DQ then DR are numbers, if not it will return 0.

Is this what you are looking for?

Regards

 

[Sunline]

Yes it is , that works great .
Is it possible to retain the 2nd IF and ROUNDUP , but 1st IF staement to locate text , so $FC$2:$FC$38 when changed to accomadate text , not sure if this is possible as already mentioned by Barry .
If there is one , hoping to have same lenght function as per last one . Thanks heaps Devonknows

 

[barry houdini]

It doesn't matter whether DO2, DP2 etc are numbers or text. If there is a match then MATCH returns a number (the position of the match with the lookup range) so you always use ISNUMBER, whatever the type of data.

If you just want to use The ROUNDUP function if any data in DO2:DR2 matches any data in $FC$2:$FC$38 then you could try like this

=IF(AND(SUMPRODUCT(COUNTIF($FC$2:$FC$38,DO2:DR2))>0,EG2>=0,EG2<=10),21-ROUNDUP(EG2/0.5,0),0)

 

[Sunline]

Thanks Barry and Devonknows , functions work great .