how to merge multi If with Vlookup ?

[belal]

Dears,
kindly I want to fill Column sub Category 1 & 2 from another sheet with the Following Condition


Sub Category 1 will be :
if (Activity coloumn = 211 or 212) : COGS
if (Activity [FONT=&quot]coloumn[/FONT][FONT=&quot]= 111 or 231) : G&A[/FONT]
if (Activity [FONT=&quot]coloumn[/FONT][FONT=&quot]= 111 or 231 & Cost center = 312 & 341 ) : S&M

i think i need to make multi If Function + Vlookup [/FONT][FONT=&quot] [/FONT]

​

ِAccount Number	Activity	Cost Center	SUb Category 1	Sub Category 2
52310101 <tbody> </tbody>	211	211
53130150 <tbody> </tbody>	212	325
53140101 <tbody> </tbody>	111	400
53140103 <tbody> </tbody>	231	410
53140109 <tbody> </tbody>	231	312
53150150 <tbody> </tbody>	111	341

<tbody>
</tbody>

 

[belal]

kindly provide me with your advice

Are you looking up the activity and cost center from another sheet by the current sheet's account #?

 

[belal]

anyone can help

 

[sheetspread]

The solution in post #3 should work with some adjustment. If you need a vlookup, just substitute it for B2, C2, etc in the formula. You could also use an advanced filter, remove duplicates, or the pivot table to get a unique list of the source data. What have I overlooked?

 

[belal]

when i took the solution in post#3 and change the names like COGS & S&M & G&A, it didn't give me with the correct answer and it shows result of line 4 in line 5 also !!

The solution in post #3 should work with some adjustment. If you need a vlookup, just substitute it for B2, C2, etc in the formula. You could also use an advanced filter, remove duplicates, or the pivot table to get a unique list of the source data. What have I overlooked?

 

[belal]

could you please my example in post#8, I showed an example of what I got and what I want to reach
your support is highly appreciated

 

[sheetspread]

Serial No# Account No. Activity Cost Center
Sub Category
1 51110101 211 211 Cost of Sales
2 51110101 212 212 Cost of Sales
3 53110101 111 383 G&A
4 53110101 231 400 G&A
5 53110101 231 341 Sales & Marketing
6 51110101 111 312 Sales & Marketing


Is the source (sheet we're looking up from)?

The account #s appear multiple times with different activity and cost center numbers. How do you know which to choose? Or does each get a new subcategory (i.e. 1,2,etc)?

 

[belal]

that's what I'm talking about because cost center ( 312 & 341 ) have different treatment under Activity (231 & 111) that's why line 4 different than line 5

so the whole sheet have only 2 criteria ( account number & Activity number )
but the lines which have cost center (312 & 341 ) have extra criteria ( account number + Activity + Cost Center )

Serial No# Account No. Activity Cost Center
Sub Category
1 51110101 211 211 Cost of Sales
2 51110101 212 212 Cost of Sales
3 53110101 111 383 G&A
4 53110101 231 400 G&A
5 53110101 231 341 Sales & Marketing
6 51110101 111 312 Sales & Marketing


Is the source (sheet we're looking up from)?

The account #s appear multiple times with different activity and cost center numbers. How do you know which to choose? Or does each get a new subcategory (i.e. 1,2,etc)?

 

[belal]

that's why i gave an example in the beginning of the post

1) if account number XXXX have Activity (211 or 212 ) = it's subcategory will be (xxxx)
2) if the same account number have activity (111 or 231 ) = it subcategory will be (YYYY)
3)if the same account number have activity (111 or 231) and have cost center (312 or 341 ) = it's subcategory will be (zzzz)

Serial No# Account No. Activity Cost Center
Sub Category
1 51110101 211 211 Cost of Sales
2 51110101 212 212 Cost of Sales
3 53110101 111 383 G&A
4 53110101 231 400 G&A
5 53110101 231 341 Sales & Marketing
6 51110101 111 312 Sales & Marketing


Is the source (sheet we're looking up from)?

The account #s appear multiple times with different activity and cost center numbers. How do you know which to choose? Or does each get a new subcategory (i.e. 1,2,etc)?

 

[sheetspread]

So what goes in the last 2 columns?

Excel 2010
	A	B	C	D	E
1	Account Number	Activity	Cost Center	SUb Category 1	Sub Category 2
2	52310101	211	211
3	53130150	212	325
4	53140101	111	400
5	53140103	231	410
6	53140109	231	312
7	53150150	111	341
Sheet16

 

[belal]

our mapping show all Activity ( 111 & 231 ) shall be classified as G&A except combination which cost centers are ( 312 & 341 ) shall be classified as S&M

So what goes in the last 2 columns?

Excel 2010

	A	B	C	D	E
1	Account Number	Activity	Cost Center	SUb Category 1	Sub Category 2
2	52310101	211	211
3	53130150	212	325
4	53140101	111	400	G&A XXX
5	53140103	231	410	G&A XXX
6	53140109	231	312	S&M XXX
7	53150150	111	341	S&M XXX

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet16​