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]

​

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]ِAccount Number[/TD]
[TD="align: center"]Activity[/TD]
[TD="align: center"]Cost Center[/TD]
[TD="align: center"]SUb Category 1[/TD]
[TD="align: center"]Sub Category 2 [/TD]
[/TR]
[TR]
[TD][TABLE="width: 82"]
<tbody>[TR]
[TD="align: center"]52310101[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]211[/TD]
[TD="align: center"]211[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 82"]
<tbody>[TR]
[TD="class: xl66, width: 82, align: center"]53130150[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]212[/TD]
[TD="align: center"]325[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 82"]
<tbody>[TR]
[TD="class: xl66, width: 82, align: center"]53140101[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]111[/TD]
[TD="align: center"]400[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 82"]
<tbody>[TR]
[TD="class: xl66, width: 82, align: center"]53140103[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]231[/TD]
[TD="align: center"]410[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 82"]
<tbody>[TR]
[TD="class: xl66, width: 82, align: center"]53140109[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]231[/TD]
[TD="align: center"]312[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 82"]
<tbody>[TR]
[TD="class: xl66, width: 82, align: center"]53150150[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]111[/TD]
[TD="align: center"]341[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Account Number[/TD]
[TD="align: center"]Activity[/TD]
[TD="align: center"]Cost Center[/TD]
[TD="align: center"]SUb Category 1[/TD]
[TD="align: center"]Sub Category 2[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]52310101[/TD]
[TD="align: center"]211[/TD]
[TD="align: center"]211[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]53130150[/TD]
[TD="align: center"]212[/TD]
[TD="align: center"]325[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]53140101[/TD]
[TD="align: center"]111[/TD]
[TD="align: center"]400[/TD]
[TD="align: center"]G&A XXX[/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]53140103[/TD]
[TD="align: center"]231[/TD]
[TD="align: center"]410[/TD]
[TD="align: center"]G&A XXX[/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]53140109[/TD]
[TD="align: center"]231[/TD]
[TD="align: center"]312[/TD]
[TD="align: center"]S&M XXX[/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]53150150[/TD]
[TD="align: center"]111[/TD]
[TD="align: center"]341[/TD]
[TD="align: center"]S&M XXX[/TD]
[TD="align: center"][/TD]

</tbody>

Sheet16​