Index Match with Two Criteria, Mix of Multiple Correct Rows and Greater than Rows

Need help getting correct answer for the following-
I have three columns of data:
Column A shows several answers, multiple times
Column B shows ascending numbers for each of the different answers in Column A
Column C is the data I wish to return.

I wish to get the correct answer from Column C (or the answer that is less than the match), corresponding to the correct part of column B as identified by column A.
Example attached should make this clearer.

I have tried all forms of index, match and if statements, but this is beyond me as keeps throwing up the wrong answer. I can set the data table up differently if this would help.

Appreciate anyone's help with this.

thanks

Hi,

One way

Formula:

Please Login or Register  to view this content.

This returns the value for the ID where the required Rank is <= the column B rank. i.e. Type P Rank 100 returns H. If you need the next higher ID then remove the -1

Sorry, I'd use a helper cell H3 and forgot to put this formula in the solution formula. Use:

Formula:

Please Login or Register  to view this content.

In the production workbook I'd be inclined to create dynamic range names for A1:A16, B1:B16 and C1:C16 so that they adjust their size automatically and use the names in the formula.

Hi,

Have you changed the references in the original formula so that what were originally ranges B1:B16 & C1:C16 are now E1:E16 and F1:F16? And of course the original F1 & F2 will now be J1 & J2 presumably.

As for dynamic ranges you use the Name Manager and define the name thus. Take the current A1:A16 as an example. Create a name called say "Type" and define this as

Formula:

Please Login or Register  to view this content.

This means with the 'anchor' cell A1 offset this by zero rows and zero columns (effectively the anchor cell is still A1, and set the height of the range named Type to be the number of cells that the COUNTA() function evaluates to, in your example this will be 16, and set the width of the range to 1 column.

As you add new data the COUNTA() function will automatically increase its value and the named range will expand accordingly.

Hi,

I think you'd better upload your actual workbook. The only thing I can think of is that the COUNTA() function counts cells that aren't blank and it's mostly used where we know there is a contiguous set of values in every cell between the first cell and the last row which contains a value. Maybe you have some gaps.