If one cell has a specifis text and another cell has another specific text then give a specific result

[archie11236]

Hi
I am trying to get a formula to do the following example




A1-----------------------------------------A2-------------A3

2.7 oz Eau De Toilette Spray-----------Men-------------Cologne

2.7 oz Eau De Toilette Spray-----------Women---------Perfume

3.3 oz Pure Perfume Spray-------------Men------------Cologne

3.3 oz Pure Perfume Spray-------------Women--------Perfume

3.4 oz After Shave----------------------Men------------Mens Aftershave

3.4 oz After Shave----------------------Women--------Womens Aftershave

2.5 oz Deodorant Stick-----------------Men------------Mens Deodorant

2.5 oz Deodorant Stick-----------------Women--------Womens Deodorant

If a1 is equal to “Eau De Toilette” and a2 “Men” then give the results Cologne, If a1 is equal to “Eau De Toilette” and a2 “Women” then give the results Perfume, if a1 is “After Shave” and a2 is Men then give the result “Mens Aftershave” an so forth

I am using office 2010.

Thanks

 

[oldbrewer]

you need to list all products in say row 2 column T, in col U the desired wording for men, in col V the desired wording for women

then you can offset match from T1, drop down col T to find the match for product name, then match to either men column or women column

you will be virtually retyping your data but excel cannot guess that any "smelly" is cologne for men yet perfume for women

by the way columns are A,B,C etc

 

[archie11236]

well I am going to use specific text from each cell so i would need something like. if cell a1 is "Eau De Toilette and cell a2 is "men" then a3 is "cologne" if both cells are not those text values leave alone

 

[oldbrewer]

yes - is that the only one where the word changes according to men / women

eg soap, skin cleanser stay the same

suggestion search A1 for eau de toilette, if found if a2 = men, cologne,perfume - - - if not found search A1 for perfume, if found if A2 = men,cologne,perfume

fairly easy to put that in a formula

 

[Charlie45]

Enter in unused Columns Say M, N & J



Product Men Women
2.7 oz Eau De Toilette Spray Cologne Perfume
3.3 oz Pure Perfume Spray Cologne Perfume
3.4 oz After Shave Mens Aftershave Womens Aftershave
2.5 oz Deodorant Stick Mens Deordant Womens Deordant

If you're item you are looking up Columns A & B and you want to return in column C:

In C1 Enter =IF(B1="Men",VLOOKUP(A1,H1:J5,2,FALSE),VLOOKUP(A1,H1:J5,3,FALSE)) Hit enter
Copy C1 down the column

 

[archie11236]

I used =IF(AND(I2="Eau De Toilette Spray",outgoingfeed_tab_03_10_16!F2="Men"),"Cologne","") and it worked fine but I need it to find and copy any cell with "Eau De Toilette Spray" in it if I place * in the in the formula "*Eau De Toilette Spray*" it come back blank. any ideas anyone

 

[oldbrewer]

apple eau de toilette spray orange	men	cologne
apple eau de toilette spray orange	women	perfume
hand soap	men	hand soap
		formula in C1 giving cologne
	=IF(ISERROR(SEARCH("eau de toilette spray",A1)),A1,IF(B1="men","cologne","perfume"))
		formula then dragged down

<colgroup><col><col><col><col span="7"></colgroup><tbody>
</tbody>