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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]9G[/TD]
[TD]Consumer[/TD]
[/TR]
[TR]
[TD]9T[/TD]
[TD]Mobility[/TD]
[/TR]
[TR]
[TD]FF[/TD]
[TD]Mobility[/TD]
[/TR]
[TR]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl65, width: 130"]8N[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="class: xl65, width: 188"]Commercial[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl65, width: 130"]TA[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="class: xl65, width: 188"]Commercial[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl65, width: 130"]MP[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="class: xl65, width: 188"]Commercial[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl65, width: 130"]B6U[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="class: xl65, width: 188"]Commercial[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl65, width: 130"]B8J[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="class: xl65, width: 188"]Commercial[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl65, width: 130"]BAN[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="class: xl65, width: 188"]Commer[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl65, width: 130"]9G[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="class: xl65, width: 188"]Commer[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl65, width: 130"]BFF[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="class: xl65, width: 188"]Commercial[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl65, width: 130"]BAN[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="class: xl65, width: 188"]Commer[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl65, width: 130"]BGB[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="class: xl65, width: 188"]Commercial[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl65, width: 130"]9G[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="class: xl65, width: 188"]Commer[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl65, width: 130"]BTA[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="class: xl65, width: 188"]Commercial[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl65, width: 130"]9G[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="class: xl65, width: 188"]Consumer[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl65, width: 130"]BFD[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="class: xl65, width: 188"]Commer[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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))