extend search formula to mutiple search for single a cell

Hi

I have this formula and I want to extend it for multiple search and post the results to the cell that runs (eg B1):

(=IF(A1="Android Phones"; "Mobile Phones"; ""))

I want the formula to be like this:

(=IF(A1="Android Phones"; "Mobile Phones"; "") or =IF(A1="Android Tablets"; "Tablets"; "") or etc....)

I want to use 42 option with that formula. Is it possible?

UPDATE!!!
I have use playaller's formula like this and it works (thank you):

=IF(A2="Old Name 1","Old Name 2",IF(A2="New Name 1","New Name 2",IF(A2="Old Name 3","New Name 3",IF(A2="Old Name 4","New Name 4",IF(A2="Old Name 5","New Name 5",IF(A2="Old Name 6","New Name 6",IF(A2="Old Name 7","New Name 7","")))))))

This would extend your formula:

Formula:

=IF(A1="Android Phones","Mobile Phones",IF(A1="Android Tablets","Tablets",""))

To add more, replace the last set of quotes "" with the below formula extension and update the wording. Paste in B1 and pull down.

Formula:

IF(A1="Android Tablets","Tablets","")

You can keep replacing the last "" with the full above code to keep extending it. Making sure all labels are added in A1 before dragging the code down.

How many options are there? If less than 5, maybe an inclusive Lookup or an IF. If more than 5, than maybe create a table with what might be in A in column 1 and what you want returned in column 2, then do a vlookup on that table.

Originally Posted by playaller

This would extend your formula:

Formula:

=IF(A1="Android Phones","Mobile Phones",IF(A1="Android Tablets","Tablets",""))

To add more, replace the last set of quotes "" with the below formula extension and update the wording. Paste in B1 and pull down.

Formula:

IF(A1="Android Tablets","Tablets","")

You can keep replacing the last "" with the full above code to keep extending it. Making sure all labels are added in A1 before dragging the code down.

Thank I will try it right now and I will post the results

Originally Posted by ChemistB

How many options are there? If less than 5, maybe an inclusive Lookup or an IF. If more than 5, than maybe create a table with what might be in A in column 1 and what you want returned in column 2, then do a vlookup on that table.

In about 42 Categories. Is it possible to use the formula that playaller suggests me?


Thanks..

The method I proposed will deff work but is just a basic method. V-lookup or Match would be easier and more logical in the long run if you're updating the list.

If you had for example your master list in sheet 2.
In column A all of the Devices
Column B all of the converted Labels next to each device.

That way a vlookup can easily be used on sheet one column B to pull the name from your list in sheet 2 matching column A on sheet 1. This would make it easier to update the list on sheet 2 with the formula in place rather than re-updating the formula over and over in sheet one.

Ofcourse if your products don't change then 1 and done with my original method is fine. The preference is yours.

Originally Posted by playaller

The method I proposed will deff work but is just a basic method. V-lookup or Match would be easier and more logical in the long run if you're updating the list.

If you had for example your master list in sheet 2.
In column A all of the Devices
Column B all of the converted Labels next to each device.

That way a vlookup can easily be used on sheet one column B to pull the name from your list in sheet 2 matching column A on sheet 1. This would make it easier to update the list on sheet 2 with the formula in place rather than re-updating the formula over and over in sheet one.

Ofcourse if your products don't change then 1 and done with my original method is fine. The preference is yours.

I have use your formula like this, thank you it works:

=IF(A2="Old Name 1","Old Name 2",IF(A2="New Name 1","New Name 2",IF(A2="Old Name 3","New Name 3",IF(A2="Old Name 4","New Name 4",IF(A2="Old Name 5","New Name 5",IF(A2="Old Name 6","New Name 6",IF(A2="Old Name 7","New Name 7","")))))))

You're more than welcome. Glad I could help!