how to merge multi If with Vlookup ?

B

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]


​
[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]
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
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


sheetspread said:
This?

Excel 2010
ABCDE

<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"]COGS[/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"]COGS[/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[/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[/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[/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[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=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")))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
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))))


sheetspread said:
This?

Excel 2010
ABCDE

<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"]COGS[/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"]COGS[/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[/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[/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[/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[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=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")))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
[TABLE="width: 500"]
<tbody>[TR]
[TD]Serial No#[/TD]
[TD]Account No.[/TD]
[TD]Activity[/TD]
[TD]Cost Center[/TD]
[TD][TABLE="align: left"]
<tbody>[TR]
[TD]Sub Category[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]51110101[/TD]
[TD]211[/TD]
[TD]211[/TD]
[TD]Cost of Sales[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]51110101[/TD]
[TD]212[/TD]
[TD]212[/TD]
[TD]Cost of Sales[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]53110101[/TD]
[TD]111[/TD]
[TD]383[/TD]
[TD]G&A[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]53110101[/TD]
[TD]231[/TD]
[TD]400[/TD]
[TD]G&A[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]53110101[/TD]
[TD]231[/TD]
[TD]341[/TD]
[TD]Sales & Marketing[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]51110101[/TD]
[TD]111[/TD]
[TD]312[/TD]
[TD]Sales & Marketing[/TD]
[/TR]
</tbody>[/TABLE]


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
You must log in or register to reply here.

Similar threads

J
  • Solved
how to random all numbers
Replies
7
Views
265
Fluff
Fluff
Jaafar Tribak
  • Question Question
Putting an Hyperlink in a MsgBox !!
2 3 4
Replies
31
Views
5K
kamranyd
K
W
  • Solved
How many rows between each data point in a column
Replies
3
Views
549
Fluff
Fluff
J
  • Question Question
Need some help with permutation code, convert from one cell to a range
Replies
3
Views
428
johnnyL
J
P
  • Question Question
Tell Me I'm Crazy
Replies
2
Views
331
duggie33
D

Forum statistics

Threads
1,223,708
Messages
6,174,005
Members
452,542
Latest member
Bricklin

Share this page

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