INDEX MATCH with Final Value Return Change

[Morey]

Hello all,

If Array/Formula below returns a 2, change return to Released. Is it possible to integrate into formula below?

=IFERROR(INDEX('PR Report'!U:U,MATCH(A2&B2,'PR Report'!C:C&'PR Report'!E:E,0)),"")

Regards,

Morey

 

[Snakehips]

@Morey,

It might be beneficial if you could limit the rows rather than use full columns but maybe like....

=IF(FERROR(INDEX('PR Report'!U:U,MATCH(A2&B2,'PR Report'!C:C&'PR Report'!E:E,0)),"")=2 ,"Released",IFERROR(INDEX('PR Report'!U:U,MATCH(A2&B2,'PR Report'!C:C&'PR Report'!E:E,0)),""))

Hope that helps.

 

[Morey]

Snakehips,

That definitely works on my end. Since, we are at it. How do I add an additional value change? For instance, 2 = Released and 3 = Pending.

Thank you,

Morey

 

[Fluff]

Will your values always be numeric?
Also what should happen if the return is 1?

 

[RossTattersall]

If the formula just returns small integers;

=IFERROR(CHOOSE(INDEX('PR Report'!U:U,MATCH(A2&B2,'PR Report'!C:C&'PR Report'!E:E,0)),[what to return for 1], [what to return for 2], [what to return for 3] etc),"")

 

[Snakehips]

Maybe try...

=IFERROR(CHOOSE((INDEX('PR Report'!U:U,MATCH(A2&B2,'PR Report'!C:C&'PR Report'!E:E,0))),"","Released","Pending"),"")

Confirm with Ctrl + Shift + Enter

 

[Morey]

Fluff,

Will your values always be numeric?
Also what should happen if the return is 1?

Oddly, not all values are numeric. D = In Review...

Morey

 

[Morey]

Snakehips,

If the formula just returns small integers;

=IFERROR(CHOOSE(INDEX('PR Report'!U:U,MATCH(A2&B2,'PR Report'!C:C&'PR Report'!E:E,0)),[what to return for 1], [what to return for 2], [what to return for 3] etc),"")

If the formula just returns small integers;

=IFERROR(CHOOSE(INDEX('PR Report'!U:U,MATCH(A2&B2,'PR Report'!C:C&'PR Report'!E:E,0)),[what to return for 1], [what to return for 2], [what to return for 3] etc),"")

The issue I'm running into is that one of the multiple values is alpha numeric (D = In Review). How can Alpha & Numeric both be accounted for under the same formula? Possible?

Morey

 

[Fluff]

You could create a lookup table for the 2,3,D etc & then use that to return the text you want.

 

[RossTattersall]

I agree with Fluff in that case. Add a sheet with a table of what outcomes should lead to what outcomes and do something like;

IFERROR(VLOOKUP(INDEX(…),

,2,0),"")