I'm working on a Notion document that will help me rank things by a criteria. This is very easy on Excel, I can make a table of criteria thresholds and values and use a VLOOKUP to map a numerical/text value into a number and then add up all the scores.
It's pretty flexible because to add a new criteria, all I do is make the table and add another vlookup to my scoring cell. I've tried it in Notion but can't figure out any way of getting the relation/data model to work.
Ideally I could do this entirely in Notion without having to use the API (although I will resort to that if I must)
I really don't know how you may do that directly in Notion, but you can use https://notion2sheets.com/ to sync your databases with Sheets and then use regular sheets formulas. We are also working in a new feature that allows to connect a column in sheets with a column in Notion, allowing you to use sheets formulas and have the result values in Notion in sync.