An Excel Question on If Statements Or something else.?

Question

I have a situation where I have two columns of data Column A is from a result from a formula (We will call the location B11).

Now, the data unfortunately has 15 results. An IF statement can only go up to 7. And I cannot use VLOOKUP as I have to get the data to get greater than or equal to column A If my greater than or equal to euals the data then similar to VLOOKUP, it will slide over to get the value I want returned.

Here is the columns:

101    70
91      60
81      50
71      45
61      40
50      35
45      28
40      25
35      22
30      19
25      16
20      13
15      11
10       8
5         5

Two examples:

If my number is 104, I would like Excel to return 70 (From Col 2)

If my number is 76, I would like Excel to return 45 (Again, From Col. 2)

Oh, one more:

If my number is 40, I would like Excel to return 25 (Col 2 Result).

I really wish my Excel skills were as good as my Autocad Skills. I would not be asking this question.

As always, your help in this will be greatly appreciated.

AQuestionMark · Accepted Answer

Say the 2 columns of data is in A1:B15, C1 is the input cell for look up value, use this array formula
=INDEX(B1:B15, MATCH(MAX((A1:A15<=C1)*(A1:A15)), A1:A15,0))

In order to have result, it's needed to change above formula to array formula, copy the above formula to a cell, while the cell is selected hit F2 then hold down Shift and ctrl then hit enter, it will activate the formula to array formula, on the formula bar, curly brackets { } will enclose it.
{=INDEX(B1:B15, MATCH(MAX((A1:A15<=C1)*(A1:A15)), A1:A15,0))}

Please contact for more info.

Trending News

An Excel Question on If Statements Or something else.?

1 Answer