Search code examples
google-sheetsgoogle-sheets-formula

Display COLUMN number for TRUE in rank preference list in Google Sheets

I would like the option to toggle the display of compiled rank order responses in a grid between "response indicated" and "response indicated by rank number". I'm hoping for a formula solution rather than using Apps Script.

The included screenshot displays the current and desired outcome—with current as the example for "response indicated" (link to sheet below).

screenshot with current & desired results

The grid pulls from responses on a separate sheet as shown in the included screenshot. screenshot of input data

NOTE:

  • While the choice order on the input sheet is ordered 1-5, the display grid does not list the options in sort order.
  • There is a sort checkbox on map!C2 that informs whether the display grid uses the same order as input or sorts alphabetically.
  • The display grid uses separate formulas (B4 & C4—that do not reference one another) to populate.
  • The closest I've been able to generate is the first row toggling to the accurate ranking (using various combinations of QUERY, VLOOKUP, INDEX, MATCH, COLUMN)—but unable to generate an array output so that each row can create a specific response.

current C1 formula (uses named ranges):

=ArrayFormula(
  IF(
    REGEXMATCH(
      IF(autoSort,
        TRANSPOSE(QUERY(
          TRANSPOSE(QUERY(
            QUERY(inputTable,"where A is not null order by A",0),
            "select "&ARRAYFORMULA(JOIN(",","Col"&SEQUENCE(1,5,2))),)),,
          COLUMNS(QUERY(
            QUERY(inputTable,"where A is not null order by A",0),
            "select "&ARRAYFORMULA(JOIN(",","Col"&SEQUENCE(1,5,2))),)))),
        TRANSPOSE(QUERY(
          TRANSPOSE(QUERY(
            QUERY(inputTable,"where A is not null",0),
            "select "&ARRAYFORMULA(JOIN(",","Col"&SEQUENCE(1,5,2))),)),,
          COLUMNS(QUERY(
            QUERY(inputTable,"where A is not null",0),
            "select "&ARRAYFORMULA(JOIN(",","Col"&SEQUENCE(1,5,2))),))))),
      autoOptions),
    CHAR(10003),))

link to example sheet: https://docs.google.com/spreadsheets/d/1eRaRf-0n-VQ2zljqUpk38sMrFlCh19JAcv7IFMeFMw0/edit?usp=sharing

Solution

  • This should do it:

    =ARRAYFORMULA(IFERROR(VLOOKUP(B4:B&C3:K3,SPLIT(FLATTEN(input!A2:A&input!B2:F&"|"&COLUMN(input!B1:F1)-1),"|",0,0),2,0)))