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]

guys, it will not be solved ?!

 

[belal]

hopeless case!

 

[sheetspread]

It can be solved, I'm just not sure what you're trying to do. Please post a before/after example.

 

[belal]

let's think this is new sheet i'm looking to fill it, ok
and i want to create the function to fill subcategory 1

as i told you our mapping in the company is :
Activity : 211 or 212 will be COGS
Activity : 111 or 231 will be S&M
Activity : 111 or 231 with Cost center ( 312 or 341) will be S&M

for sure the name will not be only COGS or S&M or G&A , i just try to explain our map. so i want to creat equation to fill rows of new sheet from our Database.

For Example :
line 1 to 5: we need to make vlookup with two criteria (search with account number & Activity)
line 6 to 7: we need to make vlookup with three criteria ( account number & activity & Cost center )


MY Question now :
what's the equation give me this result?i want to make an equation for 30K row

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	COGS XXX
3	53130150	212	325	COGS YYY
4	53140101	111	400	G&A ZZZ
5	53140103	231	410	G&A MMM
6	53140109	231	312	S&M CCC
7	53150150	111	341	S&M AAA

<tbody>
</tbody>

Sheet16​

 

[belal]

is it clear now ?

 

[belal]

i make it clear now, look at below example and you will understand what i mean :D
Line 1 is unique ( we can search by account number & Activity)
Line 3 & 4: the same Account number & Activity but different Cost center, so it gives you different result

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	212	325	COGS XXX
3	53140101	111	400	G&A XXX
4	53140101	111	341	S&M XXX

<tbody>
</tbody>

Sheet16​

 

[sheetspread]

53140101 isn't in this table:

Excel 2010
	A	B	C	D	E
1	Serial No#	Account No.	Activity	Cost Center	Sub Category
2	1	51110101	211	211	Cost of Sales
3	2	51110101	212	212	Cost of Sales
4	3	53110101	111	383	G&A
5	4	53110101	231	400	G&A
6	5	53110101	231	341	Sales & Marketing
7	6	51110101	111	312	Sales & Marketing
Sheet2 (2)

Can you post it? Don't worry about posting too much, go for like 40-50 rows if you can.

Sounds like you want a multi column lookup, very easy to do, I just have to know where to look for it.

 

[belal]

i don't understand !!

53140101 isn't in this table:

Excel 2010

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

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

Sheet2 (2)​

Can you post it? Don't worry about posting too much, go for like 40-50 rows if you can.

Sounds like you want a multi column lookup, very easy to do, I just have to know where to look for it.

 

[sheetspread]

Post the first table with the data you're looking up. Not all 30,000 rows of course, but enough where I can see a pattern, say 30-50. Then post a results table that shows every possible condition. I'll write a formula that returns the same data, and if something's missing you can tell me and we'll adjust.