Help needed!!! Index or vlookup

[Tricker]

[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!

 

[Tricker]

Thank you Andrew. Sorry won't post in two places again.

I followed the formula from the website that you sent. I am not getting accurate number. Can you please take a look at this formula and let me know where my mistake is?

[TABLE="width: 463"]
<colgroup><col span="3"><col><col></colgroup><tbody>[TR]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]1--5[/TD]
[TD]1[/TD]
[TD]$100[/TD]
[TD]$100[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6--9[/TD]
[TD]6[/TD]
[TD]$200[/TD]
[TD]$100[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]10--19[/TD]
[TD]10[/TD]
[TD]$300[/TD]
[TD]$100[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]20--49[/TD]
[TD]20[/TD]
[TD]$400[/TD]
[TD]$100[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]50--499[/TD]
[TD]50[/TD]
[TD]$500[/TD]
[TD]$100[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]correct[/TD]
[TD="align: right"]$3,100[/TD]
[TD]=5*M6+4*M7+6*M8[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]incorrect[/TD]
[TD="align: right"]$2,800[/TD]
[TD]=SUMPRODUCT(--(O5>L6:L10), --(O5-L6:L10), N6:N10)[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Andrew Poulsom]

Use the Evaluate Formula tool to step through the formula. Then adjust your thresholds in column L so that you get the correct result.

 

[Tricker]

Thanks! It worked.

 

[Tricker]

Hi Andrew,

Need your help again! I have a multiplication formula that are in fractions. First scenario is when I refer a cell where 29*1.26 is and multiply that with 45 give me 1644, but if I multiply 45*37 it gives me 1665. There is a difference of $20. This is driving me crazy. Please help. Thanks a lot.

Scenario 1
=29*1.26
=37*45
=1644
[TABLE="width: 192"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
Scenario 2
=37*45
=1665

 

[Andrew Poulsom]

Well 29*1.26 is 36.54 not 37. If you want 37 use:

=ROUND(29*1.26,0)

 

[Tricker]

Hi Andrew,

I am trying to create a chart diagram where I can show EAT, THROW and SAVE for those shown months for those seven products. Please help!!

Thank you,


[TABLE="width: 1395"]
<colgroup><col><col span="15"></colgroup><tbody>[TR]
[TD="colspan: 16"][/TD]
[/TR]
[TR]
[TD]EAT
[/TD]
[TD="align: right"]Feb-15
[/TD]
[TD="align: right"]Mar-15[/TD]
[TD="align: right"]Apr-15[/TD]
[TD="align: right"]May-15[/TD]
[TD="align: right"]Jun-15[/TD]
[TD="align: right"]Jul-15[/TD]
[TD="align: right"]Aug-15[/TD]
[TD="align: right"]Sep-15[/TD]
[TD="align: right"]Oct-15[/TD]
[TD="align: right"]Nov-15[/TD]
[TD="align: right"]Dec-15[/TD]
[TD="align: right"]Jan-16[/TD]
[TD="align: right"]Feb-16[/TD]
[TD="align: right"]Mar-16[/TD]
[TD="align: right"]Apr-16[/TD]
[/TR]
[TR]
[TD]Apple
[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Pear
[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Mango[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Plum[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Peach[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Pomogranate [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 16"]THROW
[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Mango[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Plum[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Peach[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]153[/TD]
[TD="align: right"]291[/TD]
[/TR]
[TR]
[TD]Pomogranate [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 16"]SAVE
[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Mango[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Plum[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Peach[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Pomogranate [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[Andrew Poulsom]

Just select your data and insert a Column Chart.

 

[Tricker]

Hi Andrew,

I need your help again. There are two tables, one with Number and Fruits

Table 1

[TABLE="width: 147"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Number[/TD]
[TD] Fruits[/TD]
[/TR]
[TR]
[TD="align: right"]1234[/TD]
[TD] Apple[/TD]
[/TR]
[TR]
[TD="align: right"]1234 [/TD]
[TD]Banana[/TD]
[/TR]
[TR]
[TD="align: right"]1234 [/TD]
[TD]Pear[/TD]
[/TR]
[TR]
[TD="align: right"]1234 [/TD]
[TD]Pomegranate[/TD]
[/TR]
[TR]
[TD="align: right"]1234[/TD]
[TD] Mango[/TD]
[/TR]
[TR]
[TD="align: right"]567 [/TD]
[TD] Apple[/TD]
[/TR]
[TR]
[TD="align: right"]567 [/TD]
[TD] Banana[/TD]
[/TR]
[TR]
[TD="align: right"]567[/TD]
[TD] Pear[/TD]
[/TR]
</tbody>[/TABLE]

Another table has Case, Number and Fruits. On the Fruits cell, I need to bring all the fruits that are linked to Number 1234 in the same cell. Is that doable? They can be separated by comas.

[TABLE="width: 212"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Case[/TD]
[TD]Number [/TD]
[TD] Fruits[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]1234[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]567[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]568[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]345[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]685[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]