Use Index Match to retrieve the first larger number greater than a criteria valu

Question

Use Index Match to retrieve the first larger number greater than a criteria value

I would like to use Index Match to retrieve the first value (Col b) larger than a criteria value (Col C). The order of the "lookup" values is based upon dates in column A which are in descending date order hence the order of the "lookup" column values is random. The criteria of selection is in column c.

08-14-14

In this example for criteria value of 6 the correct answer is 10. The value of 10 is the first value greater than criteria 6 which is closest by date to the criteria date. Thus the search direction is from the oldest date up to the most recent date.

Thanks in advance for your inputs and help.

Date (A) Value (B) Criteria (C)

08-14-14

8 08-13-14 3 08-12-14 7 08-11-14 4 08-10-14 10 08-09-14 4 08-08-14 5 08-07-14 3 6

Explanation / Answer

888

=LOOKUP(2,1/($B$2:$B$9>=C9),$B$2:$B$9)

Adjust the row number 9 to the row where you type the criteria. So if your criteria is typed in cell C7, then the formula will be:

=LOOKUP(2,1/($B$2:$B$7>=C7),$B$2:$B$7)

Hope this helps.

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.