Formula help

Question

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

harun24hr · Answer

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

sergeibaklan · Answer

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" &amp; 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 &amp; Room Door", "Main Door"),
    KeyDesc,      IF(Type="Swipe Key", "Swipe Key", "Hard Key"),
    DoorDesc &amp; " "
)
)

carl_61 · Answer

I do not know how to attach the file. 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

carl_61 · Answer

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