Formula Index Match

[Excelnoob02]

• https://1drv.ms/x/s!Agmju254hK9cgxTt7kQkOE1x2QGe 

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

[#2]

@Excelnoob02 

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.

[#3]

@Hans Vogelaar 

Thanks Hans, but not quite sure what I'm missing. Think you could narrow down my mistake?https://1drv.ms/x/s!Agmju254hK9cgxZgxzwATrV5CYqU 

[#4]

@Excelnoob02 

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)),"")

[#5]

For the life of me, I still cannot get it to work. I believe I tagged office 365, but it's professional plus 2016 if that makes a difference. Here is my 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)),"")

[#6 · Accepted solution]

@Excelnoob02 

What happens if you confirm the formula by pressing Ctrl+Shift+Enter ?

[#7]

@Excelnoob02 

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.

[#8]

That worked great. Was able to get it working. Thank you for your time and help

[#9]

Awesome! Thank you for the assistance