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]
 
Sort by date Sort by votes
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
sheetspread said:
So what goes in the last 2 columns?

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

</tbody>
Sheet16​
Click to expand...
 
Upvote 0

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
belal said:
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
Click to expand...

Could you explain this part further?
 
Upvote 0
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

sheetspread said:
Could you explain this part further?
Click to expand...
 
Upvote 0
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
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"][/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​

[/QUOTE]
 
Upvote 0
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

belal said:
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
ABCDE

<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​
Click to expand...
[/QUOTE]
 
Upvote 0
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

belal said:
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
Click to expand...
[/QUOTE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
how to random all numbers
Replies
7
Views
280
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
558
Fluff
Fluff
J
  • Question Question
Need some help with permutation code, convert from one cell to a range
Replies
3
Views
437
johnnyL
J
P
  • Question Question
Tell Me I'm Crazy
Replies
2
Views
337
duggie33
D

Forum statistics

Threads
1,224,827
Messages
6,181,200
Members
453,022
Latest member
RobertV1609

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