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

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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 :(

sheetspread said:
So what goes in the last 2 columns?

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"] COGS XXX [/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 YYY [/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 ZZZ [/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 MMM [/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 CCC [/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 AAA [/TD]
[TD="align: center"][/TD]

</tbody>
Sheet16​
Click to expand...
 
Last edited:
Upvote 0
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
sheetspread said:
So what goes in the last 2 columns?

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

[TD="align: center"] 3 [/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"] 4 [/TD]
[TD="align: center"] 53140101 [/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...
 
Upvote 0
53140101 isn't in this table:


Excel 2010
ABCDE
1Serial No#Account No.ActivityCost CenterSub Category
2151110101211211Cost of Sales
3251110101212212Cost of Sales
4353110101111383G&A
5453110101231400G&A
6553110101231341Sales & Marketing
7651110101111312Sales & 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.
 
Upvote 0
i don't understand !!

sheetspread said:
53140101 isn't in this table:

Excel 2010
ABCDE
Serial No#Account No.ActivityCost CenterSub Category
Cost of Sales
Cost of Sales
G&A
G&A
Sales & Marketing
Sales & Marketing

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]51110101[/TD]
[TD="align: right"]211[/TD]
[TD="align: right"]211[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]51110101[/TD]
[TD="align: right"]212[/TD]
[TD="align: right"]212[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]53110101[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]383[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]53110101[/TD]
[TD="align: right"]231[/TD]
[TD="align: right"]400[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]53110101[/TD]
[TD="align: right"]231[/TD]
[TD="align: right"]341[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]51110101[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]312[/TD]

</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.
Click to expand...
 
Upvote 0
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.
 
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,199
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