# INDEX MATCH with Final Value Return Change

#### Morey

##### New Member
Hello all,

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

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

Regards,

Morey

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Snakehips

##### Well-known Member
@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

##### New Member
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

##### MrExcel MVP, Moderator
Will your values always be numeric?
Also what should happen if the return is 1?

#### RossTattersall

##### New Member
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

##### Well-known Member
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

##### New Member
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

##### New Member
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

Last edited:

#### Fluff

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

#### RossTattersall

##### New Member
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),"")