Help needed with Index and match

[vodada]

Hi all,
Hope someone can help please. I have written a index and match formula that works but I want to have more than 1 scenario in the formula. Thats when the formula do not work.

This works:
=INDEX(Database!$K$2:$K$6,MATCH(1,INDEX(($A18=Database!$D$2:$D$6)*(Sheet1!Z$14=Database!$L$2:$L$9)*(Sheet1!Z$17=Database!M2:M10),0,1),0))


I tried to nest 2 sets into a If statement but doesnt work. Or maybe there is another way.
=if(INDEX(Database!$K$2:$K$6,MATCH(1,INDEX(($A18=Database!$D$2:$D$6)*(Sheet1!Y$14=Database!$L$2:$L$9)*(Sheet1!Y$17=Database!M2:M10,if(INDEX(Database!$K$2:$K$6,MATCH(1,INDEX(($A18=Database!$D$2:$D$6)*(Sheet1!Y$14=Database!$L$2:$L$9)*(Sheet1!Y$17=Database!N2:N10),0)))))))


Appreciate the help in advance.
Ben

 

[Skybluekid]

What is the final result that you want?

 

[vodada]

Hi skybluekid. I am trying to match the below different criteria's for 2 different scenarios into just 1 formula.

 

[vodada]

Hi skybluekid. I am trying to merge different criteria's for 2 different scenarios into just 1 formula.

 

[Fluff]

How about

=INDEX(Database!$K$2:$K$6,MATCH(1,INDEX(($A18=Database!$D$2:$D$6)*(Sheet1!Z$14=Database!$L$2:$L$9)*((Sheet1!Z$17=Database!M2:M10)+(Sheet1!Z$17=Database!N2:N10)),0,1),0))

 

[vodada]

Hi Fluff. Thank you for your help.
What I am trying to have is to combine the below 4 separate formula. The intent is that if the first condition return false then the condition is tested and until the last condition.

=INDEX(Database!$K$2:$K$6,MATCH(1,INDEX(($A18=Database!$D$2:$D$6)*(Sheet1!Z$14=Database!$L$2:$L$9)*(Sheet1!Z$17=Database!M2:M10),0,1),0))

=INDEX(Database!$K$2:$K$6,MATCH(1,INDEX(($A18=Database!$D$2:$D$6)*(Sheet1!Z$14=Database!$L$2:$L$9)*(Sheet1!Z$17=Database!N2:N10),0,1),0))

=INDEX(Database!$K$2:$K$6,MATCH(1,INDEX(($A18=Database!$D$2:$D$6)*(Sheet1!Z$14=Database!$L$2:$L$9)*(Sheet1!Z$17=Database!O2:O10),0,1),0))

=INDEX(Database!$K$2:$K$6,MATCH(1,INDEX(($A18=Database!$D$2:$D$6)*(Sheet1!Z$14=Database!$L$2:$L$9)*(Sheet1!Z$17=Database!P2:O10),0,1),0))


I was able to do what I wanted using xlookup to perform the same task but its causing the spreadsheet to slow down quite massively hence trying to use match and index instead.

'=XLOOKUP(1,(Employeecode=$A19)*(M=Y$17)*(crew=Y$14),Leavecode,XLOOKUP(1,(Employeecode=$A19)*(N=Y$17)*(crew=Y$14),Leavecode,XLOOKUP(1,(Employeecode=$A19)*(O=Y$17)*(crew=Y$14),Leavecode,XLOOKUP(1,(Employeecode=$A19)*(P=Y$17)*(crew=Y$14),Leavecode,XLOOKUP(1,(Employeecode=$A19)*(Q=Y$17)*(crew=Y$14),Leavecode,XLOOKUP(1,(Employeecode=$A19)*(S=Y$17)*(crew=Y$14),Leavecode,XLOOKUP(1,(Employeecode=$A19)*(SS=Y$17)*(crew=Y$14),Leavecode,XLOOKUP(1,(Employeecode=$A19)*(U=Y$17)*(crew=Y$14),Leavecode,XLOOKUP(1,(Employeecode=$A19)*(V=Y$17)*(crew=Y$14),Leavecode,XLOOKUP(1,(Employeecode=$A19)*(W=Y$17)*(crew=Y$14),Leavecode,XLOOKUP(1,(Employeecode=$A19)*(X=Y$17)*(crew=Y$14),Leavecode,XLOOKUP(1,(Employeecode=$A19)*(Y=Y$17)*(crew=Y$14),Leavecode,XLOOKUP(1,(Employeecode=$A19)*(Z=Y$17)*(crew=Y$14),Leavecode,XLOOKUP(1,(Employeecode=$A19)*(AA=Y$17)*(crew=Y$14),Leavecode),XLOOKUP(1,(Employeecode=$A19)*(AB=Y$17)*(crew=Y$14),Leavecode,XLOOKUP(1,(Employeecode=$A19)*(AC=Y$17)*(crew=Y$14),Leavecode,XLOOKUP(1,(Employeecode=$A19)*(AD=Y$17)*(crew=Y$14),Leavecode),XLOOKUP(1,(Employeecode=$A19)*(AE=Y$17)*(crew=Y$14),Leavecode,XLOOKUP(1,(Employeecode=$A19)*(AF=Y$17)*(crew=Y$14),Leavecode,XLOOKUP(1,(Employeecode=$A19)*(AG=Y$17)*(crew=Y$14),Leavecode,"T"))))))))))))))))))

 

[Fluff]

Did you try my suggestion?
Also I doubt that index/match will be any faster than xlookup.

 

[vodada]

Yes I did try your formula and it shows up when the formula is in cell Z18 but does not show when I put the formula in cell W18 onwards.

 

[Fluff]

I have no idea what you mean by that.