vlookup with mutiple matching rows

[blakeyoung]

How can you use VLOOKUP to return a specific row when more than one row matches the criteria? For example, if given:

Please Login or Register  to view this content.

I want to return the value from column C for the user Bob when the value of B is as close to 12 as possible without going over. In this case, 50.

Is there any way to do this using formulas? I would really rather avoid using a macro in this case if it's possible.

You can assume the values in column B will always be sorted in increasing order, as shown.

[rylo]

Hi

Try this array entered (ctrl, shift, enter) formula

Please Login or Register  to view this content.

Or this, which I think doesn't require the data to be sorted.

Please Login or Register  to view this content.

rylo

[shg]

Try this array formula:

=INDEX(C1:C9, MATCH(12, IF(A1:A9="Bob", B1:B9)))