Key Issue Form Fill In

Hello, Community, I could use some help please. 

I am trying to have a Form filled in based on key issues in my Key Log.  I have a sheet called "Key Log" and a sheet called "Key Issue Log".  When transactions are made in the KEY LOG. I'd like to have those transactions show on my Key Issue Form.  There are various cells that will need to be filled along with the key transactions.  

I have been working on what is called a Temporary Key Issue Form that currently gets filled out by hand, but I am trying to automate it.  Our rooms use either Plastic Swipe Keys or Hard Keys to gain entrance into the rooms.  Once these keys are issued out to an individual, they are required to acknowledge receipt of the keys, and this form is used for this acknowledgment.   In excel, I have a sheet called Key Log where the keys get recorded to account for their whereabouts.  In this KEY LOG, I have the following columns: 

ID, Name, Room, Date, (Out, In, Lost, Found, Defective), Key Type, Select Swipe Key 1st 3 Digits, Select Swipe Key Number, Hard Key Number, Key Tag Number, Hard Key or Swipe Key Number, Notes, Key Form/Lost Chrg Completed, Column 1 (Blank Column), Status(Helper Column), Swipe Card Prefix 1(Helper Column), Swipe Card Prefixes 2(Helper Column).  The columns are in this order with these Headers in row 3 starting at A3. The data starts in A4 which is the ID number. I have a few issues I am trying to overcome and hoped you could help.

The Temporary Issue Receipt is on a sheet called Key Issue Form. On this form, based on the ID number of the individual selected from a drop down in K6, the ID number is placed in cell D6 on the form. Doing so, see the formulas for D4 and A6.  Starting in cell A8:A18 are the key numbers pulled from the KEY Log sheet transactions that are associated with the ID number & room number.  In cells D8:D18 is where specific descriptions are to be listed based on the transaction on the Key Log sheet.  For instance: on the Key Log sheet if the room number selected is for example Room 126 (3 numbers only) from column C, along with the date, Column D, the action of the transaction, Column E, (Out), the Key Type, (Hard Key) will pullup the Key Number, Column I & the Tag Number. Column J.  The final result of the key number shows in column K.  The key number would be in column A8. The Description for this example would be:

Main Door Hard Key - Out, 2/9/25

If the selected key type was Swipe Key, it would say

Main Door Swipe Key - Out, 2/9/25

If the room number is, for example, 236A, the description would be

Combined Main & Room Door Hard Key - Out, 2/9/25

If the Key Type selected was Swipe Key, the Description would be 

Combined Main & Room Door Swipe Key - Out, 2/9/25

The description is built by the selections made in the Key Log.

The type of transactions that are selectable are Out, In, Lost, Found, Defective. So that means different transactions are possible based on the action selected in column F on the Key Log.  Examlpe:  Main Door Swipe Key - Lost, 6/10/25, Main Door Hard Key - Lost, 6/10/25

Main Door Swipe Key - Found, 10/7/25.

Remember, if the room number selected in column C on the Key Log is just 3 digits, the 1st part of the description would be Main Door.  If the key number has an Alpha Character, such as A or B, The Description would start as Combined Main & Room Door with the rest of the description being defined based on the selections from the Key Log.

The following formula in cell B8 is what I have but it's not doing the job: This formula and many other versions have been offered to me via Chat GPT but ChatGPT has not gotten it correct thus far.

=IF(A8="","",
LET(
    KeyNum, A8,
    ID, 'Key Issue Form'!D6,
    LogKeys, 'Key Log'!$K$4:$K$309,
    LogIDs, 'Key Log'!$A$4:$A$309,
    LogTypes, 'Key Log'!$F$4:$F$309,
    LogStatus, 'Key Log'!$E$4:$E$309,
    LogDates, 'Key Log'!$D$4:$D$309,
    LogRooms, 'Key Log'!$C$4:$C$309,

    MatchRows, FILTER(ROW(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,1)="A", RIGHT(Room,1)="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"))
))

Formula in Cell A8

=LET(
    MatchRow, SMALL(
        IF(
            ('Key Log'!$A:$A=$D$6) *
            ('Key Log'!$E:$E="Out") *
            ('Key Log'!$N:$N=""),
        ROW('Key Log'!$K:$K)),
    ROW(A1)),

    KeyType, INDEX('Key Log'!$F:$F, MatchRow),
    KeyNum, INDEX('Key Log'!$K:$K, MatchRow),

    IFERROR(
        IF(KeyType = "Hard Key", "V" & KeyNum, KeyNum),
        ""
    )
)

A6 formula:

=IF(D6="","",INDEX(ALL!$I3:$I170, MATCH(D6, ALL!$H3:$H170, 0)) & ", " & INDEX(ALL!$L3:$L170, MATCH(D6, ALL!$H3:$H170, 0)))

Formula in Cell D6

=IF(K6="","",VALUE(LEFT(K6,FIND(" -", K6)-1)))

Formula in Cell D4

=IF(D6="","", INDEX(ALL!J3:J170, MATCH(D6, ALL!$H3:$H170, 0)))

As an example, the individual shown on the Key Issue Form attached, has an ID number of 58959988 and room number 210B. If you were to filter room 210B you will see a number of transactions that should be showing up on the Key Issue Form with the various Descriptions in B8:B18 and the Key Numbers in cells A8:A18.

I hope my lengthy description provides you at least most of the information you need to help me out. If not, please let me know your questions

This is the Key Log Filtered to this one individual

I don't know how to publish my example data and form

Car;l