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('Key Log'!$A$4:$A$309)=TRIM($D$6))*(TRIM('Key Log'!$C$4:$C$309)=TRIM($K$15))),
KeyNums,FILTER('Key Log'!$K$4:$K$309,(TRIM('Key Log'!$A$4:$A$309)=TRIM($D$6))*(TRIM('Key Log'!$C$4:$C$309)=TRIM($K$15))),
IF(INDEX(KeyTypes,ROW(A1))="Hard Key","V"&INDEX(KeyNums,ROW(A1)),INDEX(KeyNums,ROW(A1)))
),"")

This formula resides in cells A8:A18 which is designed to return a key number, whether the key be a swipe key or a hard key.  Cells B8:B18 are for descriptions of the key identified in cells A8:A18.

This formula is supposed to find the 1st match on the key log and then find any other matches and place them in cells A8:A18.  There is another formula for defining the description based on criteria.  I need this formula to search out all transactions on the key log that match the ID number on the Key Issue Form in cell D6 with the cells in column A on the ID number and to also match the room number from K15 on the Key Issue Form with the room number in column C on the key log.  There is something missing from this formula that is not allowing the other transactions to be found and listed on the Key Issue Form.

Can anyone help me complete this formula?

 

This is the formula for B8:B18

=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,1)="A",RIGHT(Room,1)="B"),
DoorDesc,IF(IsAB,"Combined Main & Room Door","Main Door"),
KeyDesc,IF(Type="Swipe Key","Swipe Key","Hard Key"),
DoorDesc & " "

 

Can someone help me figure this out?

Carl

excel

2 Replies

  • 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 & " "
)
)
  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Nov 29, 2025

    Can you please attach a sample file or share via OneDrive, Google-Drive, Dropbox or similar service?

Resources