Formual Help

[steB2]

Hi all,

My minds gone blank and can't figure out my issue.

This is my current formula =INDEX('Rate Card'!$E:$E,MATCH(D2#,'Rate Card'!$D:$D,0))

I am trying to amend this to check column Z first and

if column Z =2022 then do this formula calculation =INDEX('Rate Card'!$E:$E,MATCH(D2#,'Rate Card'!$D:$D,0))
if column Z =2023 then do this formula calculation =INDEX('Rate Card'!$F:$F,MATCH(D2#,'Rate Card'!$D:$D,0))

 

[RudRud]

=IFS(Z2=2022,INDEX('Rate Card'!$E:$E,MATCH(D2#,'Rate Card'!$D:$D,0)),Z2=2023,INDEX('Rate Card'!$F:$F,MATCH(D2#,'Rate Card'!$D:$D,0)))

Or

=IFS(YEAR(Z2)=2022,INDEX('Rate Card'!$E:$E,MATCH(D2#,'Rate Card'!$D:$D,0)),YEAR(Z2)=2023,INDEX('Rate Card'!$F:$F,MATCH(D2#,'Rate Card'!$D:$D,0)))

Assuming 2022/2023 in Z2

 

[steB2]

Thank you so much, the first one worked straight away,

Could I possible trouble you for help with the next formual, same scenario but with an extra if in.

These are my current formulas
=IF(Z2#=2022,INDEX('Rate Card'!$U$2:$U$22,MATCH(I2#,'Rate Card'!$S$2:$S$22,1)),0)
=IF(Z2#=2023,INDEX('Rate Card'!$v$2:$v$22,MATCH(I2#,'Rate Card'!$S$2:$S$22,1)),0)

I want to merge same as before together but with extra condition at the beginning.

IF J2#=Order

Hope that makes sense,

 

[RudRud]

=IF(J2="A",IFS(Z2=2022,INDEX('Rate Card'!$E:$E,MATCH(D2#,'Rate Card'!$D:$D,0)),Z2=2023,INDEX('Rate Card'!$F:$F,MATCH(D2#,'Rate Card'!$D:$D,0))),"")

OR

=IF(J2="A",IF(Z2#=2022,INDEX('Rate Card'!$U$2:$U$22,MATCH(I2#,'Rate Card'!$S$2:$S$22,1)),IF(Z2#=2023,INDEX('Rate Card'!$V$2:$V$22,MATCH(I2#,'Rate Card'!$S$2:$S$22,1)),0)),"value if false")

Please change into desire condition, like this?

 

[steB2]

Thanks again RudRud, the second one worked perfect!!!!

Have a great day

 

[RudRud]

Thanks again RudRud, the second one worked perfect!!!!

Have a great day

Glad to assist, If you have any further questions, please don't hesitate to ask/share with us

 

[steB2]

Sorry can I ask you one more question Rudrud please?
I have the following formula set up but I need to add one more condition to it first.

=IF(J2#=$T$1,IF(Z2#=2022,INDEX('Rate Card'!$U$2:$U$22,MATCH(I2#,'Rate Card'!$S$2:$S$22,1)),IF(Z2#=2023,INDEX('Rate Card'!$V$2:$V$22,MATCH(I2#,'Rate Card'!$S$2:$S$22,1)),0)),"£0.00")

If X2#=0 is what I need to add to the beginning so that if X2# is 0 then continue with formulas, if X2 is is not 0 then "Invoiced"

Hope that makes sense

 

[RudRud]

=IF(X2=0,IF(J2#=$T$1,IF(Z2#=2022,INDEX('Rate Card'!$U$2:$U$22,MATCH(I2#,'Rate Card'!$S$2:$S$22,1)),IF(Z2#=2023,INDEX('Rate Card'!$V$2:$V$22,MATCH(I2#,'Rate Card'!$S$2:$S$22,1)),0)),"£0.00"),"Invoiced")

 

[steB2]

Superstar again, thank you