Index Match Query

[ireland87]

Hi

I'm trying to have an index match or equivalent whereby in cell B5 in looks for a daily task then if there is one displays "Daily Task"

In cell B6 it then needs to look for a Post Task or a Month task

likewise in B7

I have the index match for looking to see if theres a task for each system but cant seem to link it together for what i need. Can anyone help?

IF(INDEX(DailyTask,MATCH(E1,System,0))<>"N/A","Daily Task"
IF(INDEX(PostTask,MATCH(E1,System,0))<>"N/A","Post Task"
IF(INDEX(MonthTask,MATCH(E1,System,0))<>"N/A","Monthly Task"

 

[Special-K99]

Try

=IF(INDEX(DailyTask,MATCH(E1,System,0))<>"N/A","Daily Task",IF(INDEX(PostTask,MATCH(E1,System,0))<>"N/A","Post Task",IF(INDEX(MonthTask,MATCH(E1,System,0))<>"N/A","Monthly Task")))

though I dont understand why B5 and B6 and B7 dont exist in your formula?

 

[tyija1995]

I think ireland87 has used named ranges for B5/B6/B7 so that would be why

 

[ireland87]

Hi

if this is in B5
=IF(INDEX(DailyTask,MATCH(E1,System,0))<>"N/A","Daily Task",IF(INDEX(PostTask,MATCH(E1,System,0))<>"N/A","Post Task",IF(INDEX(MonthTask,MATCH(E1,System,0))<>"N/A","Monthly Task")))

In B6 in needs to check if B5 is populated with option 1 "Daily Task" and continue but not duplicate itself
For example if B5 = Daily Task then I need the formula to move onto the next option i.e. Montly Task

and the same for B7

If i define B5 as Daily Task
B6 as Post Task
B7 as Month Task

would that help?

any ideas?

 

[ireland87]

If i combine some IF statements in B6 unfortunately it displays FALSE

for example if B5 = =IF(INDEX(DailyTask,MATCH(E1,System,0))<>"N/A","Daily Task",IF(INDEX(PostTask,MATCH(E1,System,0))<>"N/A","Post Task",IF(INDEX(MonthTask,MATCH(E1,System,0))<>"N/A","Monthly Task")))

Then B6 = =IF(B5="Daily Task",IF(INDEX(PostTask,MATCH(E1,System,0))<>"N/A","Post Task",IF(INDEX(MonthTask,MATCH(E1,System,0))<>"N/A","Monthly Task")),IF(B5=Post Task",IF(INDEX(MonthTask,MATCH(E1,System,0))<>"N/A","Monthly Task"),""))

 

[ireland87]

i've sorted it out

thanks for your help