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]

please could you give a hand, as i need it urgently for my work, the sheet include around 30K row so it's too long

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

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

Sheet16​

 

[belal]

؟؟؟؟

 

[sheetspread]

if i put equation for Vlookup he will get all result from the first line, in other words if put vlookup he will give all result will be G&A withour consider other condition in Cost center

Could you explain this part further?

 

[belal]

did you saw post # 8?
this is like sheet I had, so when I make your suggest equation and replace "COGS" & " S&M" & "G&A" with Equation Vlookup, the result wasn't correct.
line 6 will have the same result of line 5, which is not correct. on another side, i replace above words with vlookup cause the name change so i want the name in the Data base

i clarify my case in post #8 & 18 & 20

Could you explain this part further?

 

[belal]

that's how it shall be
All lines have only two criteria (Account number & Activity ) but if the cost center include (312 & 341) it's treated with another exception

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​

[/QUOTE]

 

[belal]

all lines from 1 to 5 have no problem, just make an equation with 2 criteria (Account number & Activity ) but in line 6 & 7 they include cost center ( 312 & 341) that's why there new result

that's how it shall be
All lines have only two criteria (Account number & Activity ) but if the cost center include (312 & 341) it's treated with another exception

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

<tbody>
</tbody>

Sheet16​

[/QUOTE]

 

[belal]

what do you think i shall do? and i can't make a pivot table and filter...cause it's a huge data, so i need to create a function for every row

all lines from 1 to 5 have no problem, just make an equation with 2 criteria (Account number & Activity ) but in line 6 & 7 they include cost center ( 312 & 341) that's why there new result

[/QUOTE]

 

[belal]

Dears,

your support is highly appreciated

 

[belal]

???

 

[belal]

Dears,

anyone can help