Matching and Ranking

Hello,

I am unable to create a solution/formula for the attached example.

It involves matching by criteria, searching through a data set, then returning the result based on the ranking number (using small/large function).

Please see the attached file.

Does someone have a good link to help me learn this type of forumla(s)...?

Thank you and have a great weekend,
Joe

Attached is a spreadsheet that accomplishes what you were looking for... I had to make two changes to your base data. I had to sort the data by column A, this allows for the ranking formulas to work with a single array. I also had to add a new column of numbers. This new column adds a random fractional value if the number is duplicated elsewhere in the list. This allows the lookup functions to work. Otherwise, the lookup functions wouldn't know which identical match to return when.

Hope this helps.

Hi PSUMVP,

Thanks for your solution.

I was reviewing the formulas you used for the solution under "Criteria Type C" and wanted to know how the forumla in H20 can be changed, so that it references the "Type C" or "any" criteria from cell G18 and then somehow accounts for any other data for in the set wihtin A:A, rather than specifically listing each unique description of "Type B", "Type C", etc.

Please Login or Register  to view this content.

The data/description info in column A:A will be changing, so this is why I am asking if there is a way to rewrite the formula.

I hope this makes sense.

Thanks for the help!
Joe

Hi Everybody,

Anybody have any ideas how I can rework this formula?

Thanks,
Joe

=IF(ISERROR(SMALL(OFFSET($D$3,COUNTIF(A:A,$AA$1)+COUNTIF(A:A,$AB$1),0,COUNTIF(A:A,$AC$1),1),ROW(M1))),"",SMALL(OFFSET($D$3,COUNTIF(A:A,$AA$1)+COUNTIF(A:A,$AB$1),0,COUNTIF(A:A,$AC$1),1),ROW(M1)))

Where: cell AA1="Type A", AB1="Type B" AC1="Type C"

Obviously you can choose any three cells that are nicely convenient for you rather than AA1 AB1 and AC1.

You can then set the three criteria to be whatever you want.

Hi Mark@Work,

Great idea! I will try your suggestion...

Have a great weekend,
Joe