Using Index and Match, returning a value but changing this value based on what is returned.

[HeXiao]

Hi all

I am using Index and Match to return values off another sheet, e.g

=INDEX(!Sheet1A:A,MATCH(D2,!Sheet1B:B))

However there are some values that are being returned which I want to appear differently, these values are:
0
1900

I would like 0 to appear as "X" and 1900 to appear as "Y". All other values are fine and display as I want them.

Is there anyway I can use an IF condition with this INDEX and MATCH statement together to return X and Y value if anything with 0 or 1900 appear?

Thank you

 

[Aladin Akyurek]

Like this...

=IFERROR(VLOOKUP(INDEX(Sheet1!A:A,MATCH(D2,Sheet1!B:B,1)),{0,"X";1900,"Y"},2,0),INDEX(Sheet1!A:A,MATCH(D2,Sheet1!B:B,1)))

 

[RatExcel]

Maybe it's not most elegant solution but it should work.

=IF(INDEX(!Sheet1A:A,MATCH(D2,!Sheet1B:B))=0,"X",IF(INDEX(!Sheet1A:A,MATCH(D2,!Sheet1B:B))=1900,"Y",INDEX(!Sheet1A:A,MATCH(D2,!Sheet1B:B))))