Help needed with Index and match

vodada

New Member
Joined
May 8, 2023
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Hi skybluekid. I am trying to match the below different criteria's for 2 different scenarios into just 1 formula.
 
Upvote 0
Hi skybluekid. I am trying to merge different criteria's for 2 different scenarios into just 1 formula.
 
Upvote 0
How about
Excel Formula:
=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))
 
Upvote 0
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"))))))))))))))))))
 
Upvote 0
Did you try my suggestion?
Also I doubt that index/match will be any faster than xlookup.
 
Upvote 0
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.
 
Upvote 0
I have no idea what you mean by that.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top