Stumped on how to use an Index and Match statement within an IF Function

[clawren4]

I am trying to put an if statement before my index statement.

The index statement works:
=+INDEX($C$52:$C$125,MATCH(M31,$E$52:$E$125,0))

I want to put an if statement, so that if the my value in L3 can be matched to the array A52:A124, it will return the value from the index statement above. This is what I have, which returns #VALUE

IF(L3=A52:A124,(INDEX($C$52:$C$125,MATCH(M30,$E$52:$E$125,0))),0)

Help!!!!!!

 

[dreid1011]

First, I believe you need to use CTRL+SHIFT+ENTER to use that formula instead of just ENTER. Give that a try and see what you get. If you've done it correctly, you will see { } surrounding your formula.

 

[clawren4]

Awesome, thanks. That got rid of the error. Now I have to figure out why its not returning the value from the index. L3 is within A52:A124

 

[MarcelBeug]

How about:

=IF(ISNUMBER(MATCH(L3,$A$52:$A$124,0)),INDEX($C$52:$C$125,MATCH(M30,$E$52:$E$125,0)),0)

 

[dreid1011]

Or this:

=IFERROR(INDEX(IF(L3=A52:A124,$C$52:$C$125,""),MATCH(M30,$E$52:$E$125,0)),"")

CTRL+SHIFT+ENTER

 

[clawren4]

Thank You! That works, but im trying to fix the first problem still, how to only return the value for the row in which L3 equals or matches the row in the array. Other rows have the same index value. I dont know if this makes sense

 

[dreid1011]

Thank You! That works, but im trying to fix the first problem still, how to only return the value for the row in which L3 equals or matches the row in the array. Other rows have the same index value. I dont know if this makes sense

Who were you replying to? Did you try Marcel's formula in post #4 or mine in post #5?

 

[MarcelBeug]

Additionally, it would be helpful if you could describe your requirements. Based on the info in #6 I start wondering if you shouldn't use SUMIFS.
Than again, the ranges have different sizes (A52:A124 versus E52:E125), or would that be a mistake?