Advanced Excel Formula Help

Question

I am trying to build a formula that is essentially a vlookup on two criteria. The issue is the second criteria where the rate will be "equal to or greater than"

Sheet 1: Formula is in Column E

	B	C	D	E
1	Rate Card	Spend	Blank	Range
2	RC2	23,472.47		#N/A
3	RCI	5,082.80		#N/A
4	LURC	1,485.54		#N/A
5	LURC	8,937.91		3
6	RC1	12,779.78		#N/A

attempted Formula in E:

=IF(C3=0,"",VLOOKUP(B3&"|"&IFERROR(VLOOKUP(ROUND(C3,2),'Rate Cards'!E:E,1,TRUE),C3),'Rate Cards'!B:G,6,FALSE))

I Understand why this isn't working.

Additional desired result in Range.C3 is less than the minimum in E2, so we want a blank "" result.

Sheet 2: Ranges - using the Helper in Column A =D2&"|"&E2

	A	D	E	F	G	H
1	Month Range Helper	Rate Card	Floor	Blank	Range	% of Spend
2	RC1	7500	RC1	7,500.00		1	20%
3	RC1	25000	RC1	25,000.00		2	19%
4	RC1	35000	RC1	35,000.00		3	18%
5	RC1	45000	RC1	45,000.00		4	17%
6	RC1	55000	RC1	55,000.00		5	16%
7	RC1	100000	RC1	100,000.00		6	15%
8	RC2	10000	RC2	10,000.00		1	20%
9	RC2	25000	RC2	25,000.00		2	19%
10	RC2	35000	RC2	35,000.00		3	18%
11	RC2	45000	RC2	45,000.00		4	17%
12	RC2	55000	RC2	55,000.00		5	16%
13	RC2	100000	RC2	100,000.00		6	15%
14	LURC	2000	LURC	2,000.00		1	50%
15	LURC	5000	LURC	5,000.00		2	40%
16	LURC	7500	LURC	7,500.00		3	30%
17	PCRC	22460	PCRC	22,460.00		1	20%

Answer 1

I have solved the issue.

The final formula is as follows:

Answer 2

This does not work. It pulls a range from a different rate card. It is because the number has be greater than or equal to, which the formula isn't accounting for.

Answer 3

Change the last input from 1 to -1.

Answer 4

Ashish,

This is getting us closer, but the formula is not pulling the correct ranges.

See how your Range for RC2 is pulling Range 2? The 23,472 spend is less than the floor of 25,000.00 in Range 2. The formula should be pulling Range 1 for this result.

Additionally, if a range does not meet the minimum criteria on the rate card (greater than or equal to the floor), the result should pull a blank""

Answer 5

Hi,

In cell E2, enter this formula

=XLOOKUP(B2&C2,'Rate cards'!$A$2:$A$17&'Rate cards'!$B$2:$B$17,'Rate cards'!$D$2:$D$17,"",1)

Rate card sheet is