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>

 

[mse330]

How about this in C1

=IF(AND(RIGHT(B1,3)="mer",A1="9G"),"SG",A1)

 

[Aswinraj]

Thank you @mse330 it works.., let me incorporate this with my existing formula.

 

[Aswinraj]

Hi mse330,

Now the formula is if B column contains "Mer" and A column contains 9G then it will display "SG".

Just to learn, is it possible to have two condition..?? I mean, if B column contains "Mer" and A column contains "9G" and "GG" then it should display "SG".

 

[Aladin Akyurek]

Something like...

=IF(AND(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH("9G","GG",$A1))),ISNUMBER(SEARCH("mer",$B1)),"SG","")

 

[mse330]

Hi mse330,

Just to learn, is it possible to have two condition..?? I mean, if B column contains "Mer" and A column contains "9G" and "GG" then it should display "SG".

So column A should include both "9G" & "GG" or either one ?

 

[Aswinraj]

@mse330 I found it., =IF(AND(RIGHT(B13,3)="mer",A13="9G",A13="GG"),"SG",A13) - it works.

But i Missed to provide some informations, sorry.., Actually it needs to pickup the data from one sheet to another sheet.
Please help..,


Example:
Sheet 1 Data:

It contains 3 Columns A, B and C..,

If Last 3 Letters in Column B is "mer" and Column A is "9G" and "GG"

Sheet 2 Data

If Last 3 Letters in Column B is "mer" and Column A is "9G" and "GG" then it has to pickup the data from Column C - Book 1.

Like a Vlookup.

With Regards,
Aswinraj A

 

[mse330]

@Aswinraj

I don't think this formula is correct/logical

=IF(AND(RIGHT(B13,3)="mer",A13="9G",A13="GG"),"SG",A13) … This will never return "SG" as it is evaluating the content in cell A13 to be "9G" & "GG" … Maybe this is what you're looking for =IF(AND(RIGHT(B13,3)="mer",OR(A13="9G",A13="GG")),"SG",A13)

What is in column C in sheet 1 ? Would a vlookup work? Or it should just take the value of column C in the same row ?​

​

 

[Aladin Akyurek]

Try to post 5 rows from Sheet1 and 5 rows from Sheet2 and describe the results which must obtain.

 

[Aswinraj]

Try to post 5 rows from Sheet1 and 5 rows from Sheet2 and describe the results which must obtain.

@Aladin Akyurek @mse330

From Sheet 1: If Last 3 Letters in Column B is "mer" and Column A is "9G" and "GG" then it has to lookup with

Sheet 2 If Last 3 Letters in Column B is "mer" and Column A is "9G" and "GG" then it has to pickup the data from Column C in Sheet 1.

Hope now its clear
Sheet 1 Table

A	B	C
9G	Consumer	50
9T	Commerical	1
FF	Mobile	8000
GG	Consumer	77

<tbody>
</tbody>

Sheet 2 Table

A	B	C
9G	Consumer	50
FT	Comm	#N/A
GG	Consumer	77
PF	Mobile	#N/A

<tbody>
</tbody>