• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help on IF AND MATCH formula with INDIRECT references

W

westend9876

Member
Can someone tell me why part of this formula returns value while the other part does not. Each IF(AND) is identical except for referencing one different cell (J159 and Q159):

=IF(AND(AA159<>"",J159="Retracement"),MATCH(AF159,INDIRECT(AE159):INDIRECT(Y159))+ROW(),IF(AND(AA159<>"",Q159="Retracement"),MATCH(AF159,INDIRECT(AE159):INDIRECT(Y159))+ROW(),""))

The second IF(AND returns value but the first one does not:
IF(AND(AA159<>"",Q159="Retracement"),MATCH(AF159,INDIRECT(AE159):INDIRECT(Y159))+ROW(),
 
Hi,

AND function returns either TRUE or FALSE, so what do you mean by not returning value, is it giving any error?

Can you post a sample file.

Regards,
 
Hi i put the word Retracement into both cells J159 and Q159 .... and the logic worked fine in both and both returned TRUE ....
As there was no file attached i had to assume things to work on rest of formula and what i find is in the match function you use only the .. lookup_value and lookup_arry but no Match_type .... i added the 0 for Match type and added a few values in the spread sheet to get the indirect to work . And by doing this can get your formula to work ... but if i remove the Match_Type in the match formulas it wont work.
so as Somendra points out a sample file is needed ... but you can also try adding the 0 to Match_Type section of the match function and see if that helps.
 
John() said:
Hi i put the word Retracement into both cells J159 and Q159 .... and the logic worked fine in both and both returned TRUE ....
As there was no file attached i had to assume things to work on rest of formula and what i find is in the match function you use only the .. lookup_value and lookup_arry but no Match_type .... i added the 0 for Match type and added a few values in the spread sheet to get the indirect to work . And by doing this can get your formula to work ... but if i remove the Match_Type in the match formulas it wont work.
so as Somendra points out a sample file is needed ... but you can also try adding the 0 to Match_Type section of the match function and see if that helps.
Click to expand...
 
John() said:
Hi i put the word Retracement into both cells J159 and Q159 .... and the logic worked fine in both and both returned TRUE ....
As there was no file attached i had to assume things to work on rest of formula and what i find is in the match function you use only the .. lookup_value and lookup_arry but no Match_type .... i added the 0 for Match type and added a few values in the spread sheet to get the indirect to work . And by doing this can get your formula to work ... but if i remove the Match_Type in the match formulas it wont work.
so as Somendra points out a sample file is needed ... but you can also try adding the 0 to Match_Type section of the match function and see if that helps.
Click to expand...
Hi. This option worked. I forgot the Match type. Thanks a bunch.
 
You must log in or register to reply here.
Back
Top