Help needed!!! Index or vlookup

[Tricker]

Table 1 ID	Country
1	United States
2	United States
3	United States

<colgroup><col><col></colgroup><tbody>
</tbody>

Table 2

ID	Final Region	Sub-region	user
1	Americas	United States	5
2	Americas	canada	3
3	Americas	Latin America	1

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

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"

<tbody>
</tbody>

PLEASE HELP!

 

[Andrew Poulsom]

Yes, but why?

 

[Tricker]

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.

Sheet 1
IDS	Region	Price
123	Asia	1
456	America	2
789	Europe	3
Sheet 2
IDS	Region	Price	Price from Sheet 1	Difference
123	Asia	4	1	3
456	America	5	2	3
789	Europe	6	3	3

<colgroup><col span="3"><col><col></colgroup><tbody>
</tbody>

 

[Andrew Poulsom]

What version of Excel do you have?

 

[Tricker]

What version of Excel do you have?

Microsoft Office Excel 2007

 

[Andrew Poulsom]

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

 

[Tricker]

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

 

[Andrew Poulsom]

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.

 

[proficient]

Good work sir...

 

[Tricker]

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.

Users	Price
1-5	$100
6-9	$200
10-19	$300
20-49	$400
50-499	$500

<tbody>
</tbody>

 

[Andrew Poulsom]

See if you can adapt the solution here:

McGimpsey & Associates : Excel : Using SUMPRODUCT() with variable rates