Help needed!!! Index or vlookup

Tricker

Board Regular
Joined
Nov 6, 2009
Messages
56
[TABLE="width: 165"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Table 1

ID[/TD]
[TD]

Country[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]United States[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]United States[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]United States[/TD]
[/TR]
</tbody>[/TABLE]

Table 2
[TABLE="width: 273"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]Final Region[/TD]
[TD]Sub-region[/TD]
[TD]user[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Americas[/TD]
[TD]United States[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Americas[/TD]
[TD]canada[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Americas[/TD]
[TD]Latin America[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 152"]
<tbody>[TR]
[TD="width: 152"]I need to bring in "User" col from table 2 to table 1, when ID matches in both tables but when sub-region in Table 2 is "United States"[/TD]
[/TR]
</tbody>[/TABLE]
PLEASE HELP!
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Because, there is project I am working on where I have to compare IDs and region in two sheets and bring price from one sheet to the other and compare price from both sheets. So that I know what the difference is in two sheets. Not sure if that makes sense.

[TABLE="width: 387"]
<colgroup><col span="3"><col><col></colgroup><tbody>[TR]
[TD]Sheet 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]IDS[/TD]
[TD]Region[/TD]
[TD]Price[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD]Asia[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]456[/TD]
[TD]America[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]789[/TD]
[TD]Europe[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheet 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]IDS[/TD]
[TD]Region[/TD]
[TD]Price[/TD]
[TD]Price from Sheet 1[/TD]
[TD]Difference[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD]Asia[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]456[/TD]
[TD]America[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]789[/TD]
[TD]Europe[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Have you tried SUMIFS? That would work if there can be only one combination of IDS and Region. The other possibility is a consolidated pivot table:

Excel Pivot Table Tutorial -- Multiple Consolidation Ranges



Sumifs won't work. there can be one ID matching 4 regions.

Also, consolidated pivot table also wont work because i need to know the difference in price, also two sheets are totally different in terms of headings and formats.

Thanks,
RR
 
Upvote 0
SUMIFS will work if there is only one combination of ID and Region. Example:

=SUMIFS(Sheet2!C:C,Sheet2!A:A,A2,Sheet2!B:B,B2)

I think you'll find that it's a lot faster.
 
Upvote 0
Hi Andrew,

I need your help with a formula.
Below is the pricing table, I need a formula that, if a client has 15 users, then 5 users will be priced $100, 4 users will be priced at $200, and 6 users will be priced at $300. Hence my pricing is = (5*100+4*200+6*300)= $3100. Please help. Thanks a lot.
[TABLE="class: cms_table"]
<tbody>[TR]
[TD]Users[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]1-5[/TD]
[TD]$100[/TD]
[/TR]
[TR]
[TD]6-9[/TD]
[TD]$200[/TD]
[/TR]
[TR]
[TD]10-19[/TD]
[TD]$300[/TD]
[/TR]
[TR]
[TD]20-49[/TD]
[TD]$400[/TD]
[/TR]
[TR]
[TD]50-499[/TD]
[TD]$500[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

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