Need Excel formula to Pick value based on conditions

[Aswinraj]

Hi Friends,

I wonder whether any formulas available for changing the data based on certain conditions.

Example Based on Below Table:

If Last 3 Letters in Column B is "mer" and Column A is "9G" then in Column C - it has to give as "SG"
else it has to give the same data in Column A.

Column A	Column B
9G	Consumer
9T	Mobility
FF	Mobility
8N <tbody> </tbody>	Commercial <tbody> </tbody>
TA <tbody> </tbody>	Commercial <tbody> </tbody>
MP <tbody> </tbody>	Commercial <tbody> </tbody>
B6U <tbody> </tbody>	Commercial <tbody> </tbody>
B8J <tbody> </tbody>	Commercial <tbody> </tbody>
BAN <tbody> </tbody>	Commer <tbody> </tbody>
9G <tbody> </tbody>	Commer <tbody> </tbody>
BFF <tbody> </tbody>	Commercial <tbody> </tbody>
BAN <tbody> </tbody>	Commer <tbody> </tbody>
BGB <tbody> </tbody>	Commercial <tbody> </tbody>
9G <tbody> </tbody>	Commer <tbody> </tbody>
BTA <tbody> </tbody>	Commercial <tbody> </tbody>
9G <tbody> </tbody>	Consumer <tbody> </tbody>
BFD <tbody> </tbody>	Commer <tbody> </tbody>

<tbody>
</tbody>

 

[Aladin Akyurek]

In C2 of Sheet2 control+shift+enter, not just enter, and copy down:

=INDEX(Sheet1!$C$2:$C$5,MATCH($A2,IF(Sheet1!$B$2:$B$5=$B2,Sheet1!$A$2:$A$5),0))