how to merge multi If with Vlookup ?

belal

New Member
Joined
Mar 13, 2016
Messages
38
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]:confused::confused::confused:[FONT=&quot] [/FONT]


ِAccount NumberActivityCost CenterSUb Category 1Sub Category 2
52310101

<tbody>
</tbody>
211211

53130150

<tbody>
</tbody>
212325

53140101

<tbody>
</tbody>
111400

53140103

<tbody>
</tbody>
231410

53140109

<tbody>
</tbody>
231312

53150150

<tbody>
</tbody>
111341


<tbody>
</tbody>
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
This?


Excel 2010
ABCDE
1Account NumberActivityCost CenterSUb Category 1Sub Category 2
252310101211211COGS
353130150212325COGS
453140101111400G&A
553140103231410G&A
653140109231312S&M
753150150111341S&M
Sheet3
Cell Formulas
RangeFormula
D2=IF(OR(B2=211,B2=212),"COGS",IF(AND(OR(B2=111,B2=231),OR(C2=312,C2=341)),"S&M",IF(OR(B2=111,B2=231),"G&A")))
 
Upvote 0
thanks for your help, but you forget i want to get this data from another sheet so we need to use Vlookup, and the problem 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


This?

Excel 2010
ABCDE
1Account NumberActivityCost CenterSUb Category 1Sub Category 2
252310101211211COGS
353130150212325COGS
453140101111400G&A
553140103231410G&A
653140109231312S&M
753150150111341S&M

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

Worksheet Formulas
CellFormula
D2=IF(OR(B2=211,B2=212),"COGS",IF(AND(OR(B2=111,B2=231),OR(C2=312,C2=341)),"S&M",IF(OR(B2=111,B2=231),"G&A")))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
in simple words, I want to make vlookup or any equation it's criteria

if (Account Number is the same in another sheet ) + ( Activity here is 211 or 212 or 111 or 231) give me what you found there
but there is activity = 111 or 231 and it's cost center is ( 312 or 341 ) this line have different result from the rest so i want to get it result also

=IF(OR(H17925=211,H17925=212),VLOOKUP(A17925,'[BS PL (YTD Dec 2017)-Q4-2017_180118.xlsx]Dec 2017 DP TB'!$A:$N,14,0),IF(AND(OR(H17925=111,H17925=231),OR(J17925=312,J17925=341)),VLOOKUP(A17925,'[BS PL (YTD Dec 2017)-Q4-2017_180118.xlsx]Dec 2017 DP TB'!$A:$N,14,0),IF(OR(H17925=111,H17925=231),VLOOKUP(A17925,'[BS PL (YTD Dec 2017)-Q4-2017_180118.xlsx]Dec 2017 DP TB'!$A:$N,14,0))))


This?

Excel 2010
ABCDE
1Account NumberActivityCost CenterSUb Category 1Sub Category 2
252310101211211COGS
353130150212325COGS
453140101111400G&A
553140103231410G&A
653140109231312S&M
753150150111341S&M

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

Worksheet Formulas
CellFormula
D2=IF(OR(B2=211,B2=212),"COGS",IF(AND(OR(B2=111,B2=231),OR(C2=312,C2=341)),"S&M",IF(OR(B2=111,B2=231),"G&A")))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
Are you looking up the activity and cost center from another sheet by the current sheet's account #?
 
Upvote 0
Serial No#Account No.ActivityCost Center
Sub Category

<tbody>
</tbody>
151110101211211Cost of Sales
251110101212212Cost of Sales
353110101111383G&A
453110101231400G&A
553110101231341Sales & Marketing
651110101111312Sales & Marketing

<tbody>
</tbody>


this sample from Data Base I got, and now I have a new combination of the new sheet and my goal to fill subcategory.

**Now I want to create vlookup or any equation it's major criteria is :
1)the account number
2)Activity,
>>unless the cost center is (341 & 312 )


as you can see line 4 & 5: they are the same in Account number & Activity but different in Activity so the subcategory is different

Note: the name in SUb category is not sample as ( COGS , S&M , G&A) it may have a long name.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top