Forum Discussion

Carl_61's avatar
Carl_61
Iron Contributor
Nov 28, 2025

Formula help

I have the following formula that partially works.  It processes this formula and returns the 1st match based on the formula criteria.   =IFERROR( LET( KeyTypes,FILTER('Key Log'!$F$4:$F$309,(TRIM...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Nov 29, 2025

Tried to structure your formulae a bit, but without the sample failed to understand what could be wrong

=IFERROR(
LET(
   IsD,      TRIM('Key Log'!$A$4:$A$309) = TRIM($D$6),
   IsK,      TRIM('Key Log'!$C$4:$C$309) = TRIM($K$15),
   Include,  IsD * IsK,
   KeyTypes, FILTER('Key Log'!$F$4:$F$309, Include ),
   KeyNums,  FILTER('Key Log'!$K$4:$K$309, Include ),
   keyNum,   INDEX(KeyNums, ROW(A1) ),
   IF( INDEX(KeyTypes, ROW(A1)) = "Hard Key",
       "V" & keyNum, keyNum
   )
),
"")
=IF(A8="","",
LET(
    FilteredRows, FILTER( SEQUENCE(ROWS('Key Log'!$A$4:$A$309)),
                          ('Key Log'!$A$4:$A$309=$D$6) *
                          ('Key Log'!$C$4:$C$309=$K$15)
                  ),
    RowNum,       INDEX( FilteredRows, ROW(A1) ),
    Room,         INDEX('Key Log'!$C$4:$C$309, RowNum),
    Status,       INDEX('Key Log'!$E$4:$E$309, RowNum),
    Type,         INDEX('Key Log'!$F$4:$F$309, RowNum),
    DateVal,      INDEX('Key Log'!$D$4:$D$309, RowNum),
    IsAB,         OR( RIGHT(Room)= {"A","B"} ),
    DoorDesc,     IF(IsAB, "Combined Main & Room Door", "Main Door"),
    KeyDesc,      IF(Type="Swipe Key", "Swipe Key", "Hard Key"),
    DoorDesc & " "
)
)
  • Carl_61's avatar
    Carl_61
    Iron Contributor
    Dec 05, 2025

    I don't know how to attach the sample data. Please assist.

     

    This formula is in cell B8 and works but only displays the description for the 1st key number found in A8. The formula gets drug down from B8:B18.

    =IF(A8="","",
    LET(
      keyNum, A8,
      cleanKey, IF(LEFT(keyNum,1)="V", MID(keyNum,2,99), keyNum),
      idVal, 'Key Issue Form'!$D$6,
      roomVal, 'Key Issue Form'!$K$14,

      logKeys, 'Key Log'!$K$4:$K$1000,
      logIDs, 'Key Log'!$A$4:$A$1000,
      logRooms, 'Key Log'!$C$4:$C$1000,
      logTypes, 'Key Log'!$F$4:$F$1000,
      logStatus, 'Key Log'!$E$4:$E$1000,
      logDates, 'Key Log'!$D$4:$D$1000,

      matchRowsStrict, FILTER(
          ROW(logKeys)-3,
          (logKeys=cleanKey)*(logIDs=idVal)*(logRooms=roomVal)
      ),

      matchRowsRelaxed, FILTER(
          ROW(logKeys)-3,
          (logKeys=cleanKey)*(logIDs=idVal)
      ),

      matchRow, IFERROR(INDEX(matchRowsStrict, ROW(B8)-ROW($B$8)+1),
                        INDEX(matchRowsRelaxed, ROW(B8)-ROW($B$8)+1)),

      keyType, INDEX(logTypes, matchRow),
      status, INDEX(logStatus, matchRow),
      transDate, TEXT(INDEX(logDates, matchRow), "m/d/yy"),
      roomUsed, INDEX(logRooms, matchRow),

      isSuite, IF(OR(RIGHT(roomUsed,1)="A", RIGHT(roomUsed,1)="B"), TRUE, FALSE),

      desc, IF(keyType="Swipe Key",
              IF(isSuite, "Combined Main & BDRM Door Swipe Key", "Main Door Swipe Key"),
              IF(isSuite, "Combined Main & Room Door Hard Key", "Main Door Hard Key")),

      desc & " - " & status & ", " & transDate
    ))

     

    This formula is the one in A8:A18 and it appears to be working:

    =IFERROR(
      INDEX('Key Log'!$K$4:$K$1000,
        SMALL(
          FILTER(ROW('Key Log'!$K$4:$K$1000)-3,
            ('Key Log'!$A$4:$A$1000='Key Issue Form'!$D$6)*
            ('Key Log'!$C$4:$C$1000='Key Issue Form'!$K$14)),
        ROWS(A$8:A8))
      ),
    "")

    It is supposed to search thru the Key Log and find all transactions that match to the ID # and room number.  It should show row by row, any transactions that match.  So, A8 might show a swipe key number of 

    1960404

    and a hard key number of 60.  Based on the room number an appropriate description is supposed to be displayed in B8:B18. The problem however is, I'm not getting the discerptions for the keys in A8:A18 except for the B8:B18 description. I get things like #Calc!, #Ref, #N/A.

    Please help me understand how to attach the sample data.  There use to be a button for attaching a file but I do not see that anymore.

     

    Carl

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Dec 09, 2025

      Carl, formulae could be

      in column A

      =LET(
    MatchRow, SMALL(
        FILTER( SEQUENCE( ROWS(KeyLog)),
            ( KeyLog[ID] = $D$6 & "") *
            ( KeyLog[Out, In, Lost, Found, Defective] = "Out") *
            ( KeyLog[Column1] = "")
        ),
    ROW(A1)),
    KeyType, INDEX( KeyLog[Key Type], MatchRow),
    KeyNum, INDEX( KeyLog[Hard Key or Swipe Key Num], MatchRow),
    IFERROR(
        IF(KeyType = "Hard Key", "V" & KeyNum, KeyNum),
        ""
    )
)

      In column B

      =IF(A8="","",
LET(
    KeyNum,    $A8,
    ID,        $D$6 & "",
    LogKeys,   KeyLog[Hard Key or Swipe Key Num],
    LogIDs,    KeyLog[ID],
    LogTypes,  KeyLog[Key Type],
    LogStatus, KeyLog[Out, In, Lost, Found, Defective],
    LogDates,  KeyLog[Date],
    LogRooms,  KeyLog[Room],
    MatchRows, FILTER( SEQUENCE( ROWS(LogKeys)), (LogKeys=KeyNum)*(LogIDs=ID)),
    LastRow,   IFERROR(INDEX( MatchRows, COUNT(MatchRows)), NA()),
    Room,      IF( ISNA(LastRow), "", INDEX(LogRooms,  LastRow)),
    Status,    IF(ISNA(LastRow),  "", INDEX(LogStatus, LastRow)),
    DateVal,   IF(ISNA(LastRow),  "", INDEX(LogDates,  LastRow)),
    Type,      IF(ISNA(LastRow),  "", INDEX(LogTypes,  LastRow)),
    IsAB,      IF(Room="",     FALSE, OR( RIGHT(Room)={"A","B"})),
    Description,
        IF(Type="Swipe Key",
            IF(IsAB, "Combined Main & BDRM Door Swipe Key", "Main Door Swipe Key"),
            IF(IsAB, "Combined Main & BDRM Door Hard Key", "Main Door Hard Key")
        ),
    IF(Description="", "Unknown -", Description & " - " & Status & ", " & TEXT(DateVal,"m/d/yy"))
))

Resources