May 23 2023 03:16 PM
Hi All,
I am looking for a formula, was helping it could be an =iferror, index match... but I'm sure somebody may have a better one that would work.
Essentially I want:
Thank you
May 23 2023 03:27 PM
In B20:
=IFERROR(INDEX('Pull YTD From'!$C$2:$C$244, MATCH(1, ('Pull YTD From'!$A$2:$A$244=[@[ Employee]])*('Pull YTD From'!$B$2:$B$244=[@[Outbound Task]]), 0)),"")
Format as a percentage. If Excel doesn't fill the entire table column automatically, do so yourself.
May 23 2023 04:34 PM
Thanks Hans, but not quite sure what I'm missing. Think you could narrow down my mistake?https://1drv.ms/x/s!Agmju254hK9cgxZgxzwATrV5CYqU
May 24 2023 02:05 AM
You have superfluous @ characters in the formula:
=IFERROR(INDEX('Pull YTD From'!$C$2:$C$244,MATCH(1, (@'Pull YTD From'!$A$2:$A$244=[@Employee])*(@'Pull YTD From'!$B$2:$B$244=[@[Outbound Task]]),0)),"")
should be
=IFERROR(INDEX('Pull YTD From'!$C$2:$C$244,MATCH(1, ('Pull YTD From'!$A$2:$A$244=[@Employee])*('Pull YTD From'!$B$2:$B$244=[@[Outbound Task]]),0)),"")
May 24 2023 11:06 AM
May 24 2023 11:22 AM
SolutionWhat happens if you confirm the formula by pressing Ctrl+Shift+Enter ?
May 24 2023 01:02 PM
Yes, on 2016
=IFERROR(INDEX('Pull YTD From'!$C$2:$C$244,MATCH(1, ('Pull YTD From'!$A$2:$A$244=[@Employee])*('Pull YTD From'!$B$2:$B$244=[@[Outbound task]]),0)),"")
shall be entered as array formula with Ctrl+Shift+Enter.
If slightly modify and use INDEX for the second parameter of MATCH
=IFERROR(
INDEX(
'Pull YTD From'!$C$2:$C$244,
MATCH(
1,
INDEX(
('Pull YTD From'!$A$2:$A$244 = [@Employee]) *
('Pull YTD From'!$B$2:$B$244 = [@[Outbound task]]),
0
),
0 )
), "" )
it could be used as regular formula.
May 25 2023 05:56 AM
May 24 2023 11:22 AM
SolutionWhat happens if you confirm the formula by pressing Ctrl+Shift+Enter ?