Different range Criteria

[AS]

Hi Experts,

Could you please help me to making the logic for this Range. I have range of various country and need to pick values accordingly.

Range excel-

Country	L Income	U Income	Value
IN	175,001	200,000	32.44
IN	200,001	250,000	31.06
IN	250,001	300,000	29.12
IN	300,001	400,000	27.82
IN	400,001	########	26.21
USA	1	1,000	125.14
USA	1,001	1,500	107.27
USA	1,501	2,000	101.34
USA	2,001	2,500	95.76
USA	2,501	3,000	91.81
Africa	1	1,000	37.48
Africa	1,001	1,500	32.13
Africa	1,501	2,000	30.35
Africa	2,001	2,500	28.68
Africa	2,501	3,000	27.50
Africa	3,001	3,500	26.56
Africa	3,501	4,000	25.80
Africa	4,001	4,500	25.15
Africa	4,501	5,000	24.59
Africa	5,001	6,000	24.10
Africa	6,001	7,000	23.21
Africa	7,001	8,000	22.46

Output Table

Country	Income	Values
IN	190,000	32.44
USA	2800	91.81
Africa	6500	23.21

Thanks in advance

[AS]

@Clifford_Coon 

@atcodedog05  Please look at the issue.

[Clifford_Coon]

@AS , can you give a bit more on the logic?  looks like the average of the income, but which record do you want to select?

Thanks

[AS]

Hi @Clifford_Coon  thanks for reply.

We are not picking the average value, let me try with an example:

For example see the date, having different lower and upper range:

Country	Lower value	Upper value	rates
US	300	500	2
US	501	700	3
US	701	900	5
Canada	300	500	6
Canada	501	700	8

I have a criteria to fulfil - I need to pick a rate for US on the basis of range it is falling. In this case the income for US is 500 and its falling in 501 - 700. In this case the rate I would choose would be 3.

Expected output: 

Country	Income	Rates
US	505	3
Canada	605	8

Same I need to do for other countries as well. We need to go country specific wise for defining logic.

[Clifford_Coon · Accepted solution]

@AS ,

Here is a different approach here using a batch macro.

Feed the Country / Income combo in as Control Parameter (macro will run 1x for each)

Feed the lookup table in as the data feed.

The macro:

 

 

[AS]

Hi @Clifford_Coon 

You are genius. This solution is helpful. When I'm running macro alone I'm getting result but the only issue is when I try to connect this to the main solution I"m getting this error any suggestion on this. 

[Clifford_Coon]

hi @AS ,  have you configured the "Questions" tab on the macro configuration?

 

[AS]

Thanks for help