Sheet in question: https://docs.google.com/spreadsheets/d/1-4ZS3ByIM4DeLhGH0-9_l0rR_vGm9lTb95zMsofJoeE/edit#gid=0
I have a query built already, I just need help applying it to all rows with an array formula vs. dragging it down.
I have two sheets: one is a table of job posts. The other is a table of potential applicants. Applicants have fields describing the types of jobs they want to get (such as category, experience level, etc.), and job posts have the same type of fields.
I want to match the two best applicants for each job, based on how many of their fields match the job post's fields.
I've figured out a query + array formula to rank applicants and show the best options, but now I need to use an array formula so we can get this result for every row instead of manually dragging it down as we add new rows.
The formula in question that I want to apply to all rows is on the Job posts sheet under E2.
The way it currently works is: For each job post row, the query creates a virtual table of all applicants with a virtual column that scores them. Using IF, I give them a point for each row that matches the job post criteria. Then the query sorts by their score and outputs their name and URL.
I then use "Limit 1" on the query so it only outputs the best match.
For second best, third best, etc. I use offset 1, 2, 3, etc.
Bonus points if you can help me transpose the results so that instead of setting up an identical query for 2nd place and so on, I can get all the results from one query along one row. That's less important than having this apply to all rows though.
