How do I compare a specific cell with a column range and return a string associated with the column range?

Question

Thanks for reading my first question. I'm just starting out with google sheets to please bear with me. Here is the sheet I'm working with

So,this sheet if for a game I play and we assign guild attacks based on power levels. I'm looking to create a function or script and put it in Column O. I'd like this function to compare Column F to a specific cell in Column M and then return the user associated with Column F that is >= than the specific cell in Column M. Like this enter image description here I highlighted the first three as an example.

I can obviously do this manually but I takes time, and was looking to automate this process so it becomes more efficient. I've tried Vlookups, MATCH, IF and I've been unsuccessful. Any help would be greatly appreciated. Again, I'm just a beginner with google sheets so please go easy on me. :)

Answer 1

Solution

As you metioned that you would also be happy with an scripted solution I have created this script that I believe solves your issue. It has comments explaining step by step how it works:

function myFunction() {
  // Get our sheet
  var ss = SpreadsheetApp.getActive().getSheetByName('Automate Test');
  
  // Get the values of the ranges of F and M. Flat will convert the 2D array we get from getValues() into a 1D one which is easier to work with
  var valuesF = ss.getRange('F2:F16').getValues().flat();
  var valuesD = ss.getRange('D2:D16').getValues().flat();
  var valuesM = ss.getRange('M2:M16').getValues().flat();
  var valuesN = ss.getRange('N17:N31').getValues().flat();
  
  
  // We will iterate through all the players in column M to find their opponents
  for(i=0;i<valuesM.length;i++){
    // We create an empty array to be filled with the list of possible opponents to then choose a random one of the list
    var playersHigherEqual = [];
    
    // Iterate through the opponent list
    for(j=0;j<valuesF.length;j++){
      // If the opponent meets the condition
      if(valuesF[j]>= valuesM[i]){
        // Add it to the array of possible opponents
        playersHigherEqual.push(ss.getRange(j+2, 2).getValue());
      }
    }
    
    //Finally we will set the opponent by choosing a random one out of the list. Note that i+2 is because the arrays start from 0
    ss.getRange(i+2, 15).setValue(playersHigherEqual[Math.floor(Math.random()*playersHigherEqual.length)]);
  }
  
  
  // We will iterate through all the players in column M to find their opponents
  for(i=0;i<valuesN.length;i++){
    // We create an empty array to be filled with the list of possible opponents to then choose a random one of the list
    var playersHigherEqual = [];
    
    // Iterate through the opponent list
    for(j=0;j<valuesD.length;j++){
      // If the opponent meets the condition
      if(valuesD[j]>= valuesN[i]){
        // Add it to the array of possible opponents
        playersHigherEqual.push(ss.getRange(j+2, 2).getValue());
      }
    }
    
    //Finally we will set the opponent by choosing a random one out of the list. Note that i+2 is because the arrays start from 0
    ss.getRange(i+17, 15).setValue(playersHigherEqual[Math.floor(Math.random()*playersHigherEqual.length)]);
  }  
  
}

Please let me know if you also need a sheet formula solution for this question. For more information about Apps Script check out the documentation.

I hope this has helped you. Let me know if you need anything else or if you did not understood something. :)