Forum Discussion
Formula Index Match
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:
- In "OB Assignments" tab for the table in YTD% to pull from the "Pull YTD from" tab the %'s under Average Pick % based on Employee on both sheets but also the Outbound Task/Pick Area.
- For example, cell B21 would read 92% since the criteria would be under Jose Armenta and 1st Floor Mezz.
- Some of the verbiage doesn't match for Outbound task and Pick Area but I will change that so they are all exact.
- I would like for the cell if there is no value to show blank, hence the request for the iferror in front.
Thank you
What happens if you confirm the formula by pressing Ctrl+Shift+Enter ?
8 Replies
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.
Thanks Hans, but not quite sure what I'm missing. Think you could narrow down my mistake?https://1drv.ms/x/s!Agmju254hK9cgxZgxzwATrV5CYqU
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)),"")
