Function Problems

How to Automate an Excel Key Issue Form Using Lookup Formulas

Olivia MillerOlivia Miller Nov 10, 2026 869 views

Question details

The user requires an automated method to retrieve key numbers and specific text descriptions from a Key Log based on multiple criteria, including ID, room number, date, and key type.

How to Automate an Excel Key Issue Form Using Lookup Formulas
Product
Excel
Device & OS
not provided
Scenario
Building an automated tracking form to manage facility keys, which requires conditional text based on room number formats and dynamic data retrieval from a main key log database.
Observed behavior
The resulting form must dynamically generate descriptions starting with 'Main Door' for 3-digit rooms or 'Combined Main & Room Door' for rooms ending in A or B, and append the matching key details.
Before you start

Ensure your master Key Log data is formatted as an Excel Table (Ctrl+T). This allows your lookup formulas to dynamically update as new key transactions (out, in, lost, found) are added to the log.

Solution 1Recommended

Use FILTER and IF Logic for Dynamic Data Extraction

Combine Excel's FILTER function with IF statements to pull exact matches based on multiple criteria and construct conditional door descriptions automatically.

The FILTER function is the most efficient way to handle multiple lookup criteria (ID, Room, Date) simultaneously without requiring complex array formulas. By pairing it with logical functions like IF and RIGHT, you can seamlessly handle conditional text.

1
Set up conditional room descriptions

In your target description cell, start by testing the room format. Enter `=IF(OR(RIGHT(B2,1)="A", RIGHT(B2,1)="B"), "Combined Main & Room Door - ", "Main Door - ")` assuming B2 contains the room number.

2
Build the multi-criteria FILTER formula

To extract the matching key number, use the FILTER function. Append this to your existing formula using the ampersand (&): `& FILTER(KeyLog[Key Number], (KeyLog[ID]=A2)*(KeyLog[Room]=B2)*(KeyLog[Date]=C2))`.

3
Concatenate remaining key details

Complete the description by appending the status and key type variables to the end of the formula: `& " " & D2 & " " & E2` (assuming D2 is Status and E2 is Key Type).

4
Execute the formula

Press Enter. The cell will now output a complete, automated description such as 'Combined Main & Room Door - Swipe Key 1371306 Out 05/27/25'.

Use FILTER and IF Logic for Dynamic Data Extraction
Dynamic Spilling: If multiple key transactions match your criteria, the FILTER function will automatically spill the results into adjacent rows, making it perfect for generating comprehensive key issue histories.
Advanced Spreadsheet Features

Build Automated Lookup Forms with WPS Spreadsheet

WPS Spreadsheet supports advanced dynamic array functions like FILTER as well as complex multi-criteria lookups, empowering you to build automated tracking forms seamlessly without workflow interruptions.

  1. 1. Open your Key Log in WPS Office: Launch WPS Spreadsheet and open your existing master Key Log workbook.
  2. 2. Select the form field: Click the specific cell in your Key Issue Form where you want the automated description to populate.
  3. 3. Enter the formula: Type your multi-criteria FILTER or INDEX/MATCH formulas just as you would in Microsoft Excel.
  4. 4. Apply and drag: Press Enter to retrieve the data instantly, then drag the fill handle down to apply the automated lookup logic to the rest of your form.
Fully compatible with Microsoft Excel formulas (.xlsx format)Supports dynamic array functions like FILTER and XLOOKUP nativelyFree, lightweight, and features an intuitive tabbed interface
microsoft office alternative - wps office

Frequently Asked Questions

How do I test if a room number ends with A or B in my formula?

You can use the RIGHT function to check the last character. For example, `=IF(OR(RIGHT(A2,1)="A", RIGHT(A2,1)="B"), "Combined Door", "Main Door")` evaluates the rightmost character of cell A2.

Why does my multi-criteria INDEX/MATCH formula return a #VALUE! error?

In older versions of spreadsheet software, a multi-criteria MATCH using multiplication (e.g., `(ID_Range=ID)*(Room_Range=Room)`) must be treated as an array formula. You need to press Ctrl+Shift+Enter when inputting the formula instead of just Enter.

Can I pull multiple matching key transactions (Out, In, Lost) at once?

Yes. By using the FILTER function instead of VLOOKUP or INDEX/MATCH, you can extract all matching records for a specific ID and Room combination. The results will automatically spill into the cells below.