• 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.

If too much criteria

K

KIM Chanthan

Member
Any one can help to solve my problem.
I have so many plan and I want to show the next plan based on actual action which is already done.
I know how to write it with if, but i feel it looks very messy and confuse if i input all my plan which is about 30 plan and I am not sure how many criteria is limited for If function.
Please see my detail excel file

Thanks,
Chanthan
 

Attachments

  • If much criteria.xlsx
    8.6 KB · Views: 8
In A3 enter =INDEX(3:3,MATCH(REPT("Z",255),3:3))
In B3 =INDEX($2:$2;match(INDEX(3:3,MATCH(REPT("Z",255),3:3)),$2:$2,0))
and pull down as needed
 
pecoflyer said:
In A3 enter =INDEX(3:3,MATCH(REPT("Z",255),3:3))
In B3 =INDEX($2:$2;match(INDEX(3:3,MATCH(REPT("Z",255),3:3)),$2:$2,0))
and pull down as needed
Click to expand...
I think the B3 formula is wrong and will return "#N/A" result

=INDEX($2:$2;match(INDEX(3:3,MATCH(REPT("Z",255),3:3)),$2:$2,0))

and should revise as :

=INDEX($2:$2,MATCH(REPT("z",255),3:3))

Regards
Bosco
 
pecoflyer said:
Trouble is I've got to work on gut feeling, Open Office is somewhat different than Excel
Click to expand...
I don't use Open Office, but I know the INDEX() & MATCH() are almost same as Excel.

Your mistake is here (highlighted red) :

=INDEX($2:$2,MATCH(INDEX(2:2,MATCH(REPT("Z",255),3:3)),$2:$2,0))

and which is as same as this shorter one.

=INDEX($2:$2,MATCH(REPT("z",255),3:3))

p.s. The BigText we general use "z" small letter. You can go to Mr.Excel.com to search with Aladin's post, he is 1st person to use this formula on around 2000

Regards
Bosco
 
Funnily enough OO returns an NA error with =INDEX(3:3,MATCH(REPT("Z",255),3:3))
(I used the Bob Philips version with capital z)
 
Use Simple formula Offset and CountA and get the answer.
Actual Status
=OFFSET(C2,0,COUNTA(C3:J3)-1)
NExt Step
=OFFSET(C4,0,COUNTA(C3:J3)-1)
 
You must log in or register to reply here.
Back
Top