Excel/Google Spreadsheet LOOKUPIF / MATCHIF / Cell as range?

0
votes

I have the following Google Spreadsheet table:

                                 A        B       C        D
1   Users                       1-30    31-80   81-150  151-500
2   Discount %                   0%       1%      2%       3%
3   License user / month        $10   $9       $8        $7

I would like the following to be calculated:

 20    50    90
$200  $450  $720

Because 20 is between 1-30 so it calculates as 20*$10=200

And 50 is between 31-80 so it calculates as 50*$8=450

So basically I need cell A1 to be as a range/series of numbers and I want to that the formula will know to look at a "range of ranges" A1:D1 and consider each cell if <= 30 maybe or something that way.

I tried LOOKUP / MATCH but couldn't find anything that is generic enough without putting a lot of IFs in the formula.

1
excelgoogle-sheets
Change your first row to only the minimum. 1,31,81... then use hlookup with the forth criterion as true. – Scott Craner

1 Answers

2
votes

In the following sample image, change the cell number format in C2:F2 to:

0-3\0    ◄ C2 custom number format
0-8\0    ◄ D2 custom number format
0-15\0   ◄ E2 custom number format
0-5\0\0  ◄ F2 custom number format

Note that the zeroes have been 'escaped' with a backslash to avoid confusion with reserved format mask characters.

Input 1, 31, 81 and 151 into C2:F2.

cnf_range

All that remains is a simple HLOOKUP function and some maths. In H4 as,

=H3*HLOOKUP(H3, $C2:$F4, 3, TRUE)

Fill right as necessary.

cnf_range_formula