How to Perform a VBA Exact Match Lookup with an Optional Second Criterion
Question details
The user needs a VBA lookup macro that searches for an exact match of a primary criterion, prefers a specific secondary criterion if present, and defaults to a row with a blank secondary criterion if the specific one is missing.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Writing a VBA function to retrieve a value from a minimum or maximum column based on complex conditional logic involving a mandatory board type and an optional system number.
- Observed behavior
- The macro needs to accurately loop through matched primary criteria, properly qualify cell references to avoid errors, and successfully return the cell value from the targeted minimum or maximum column using the identified row index.
Ensure the Developer tab is enabled in your spreadsheet and that your workbook is saved as a Macro-Enabled Workbook (.xlsm) before inserting or testing VBA code.
Implement a VBA Lookup with Fallback Logic
Use the VBA .Find method inside a loop to search for the primary criterion, inspect the secondary column, and conditionally store the row index for the final value retrieval.
This solution utilizes a loop with the `.Find` and `.FindNext` methods to locate all instances of the primary criterion. It evaluates the secondary column for an exact match but retains the row number of a blank secondary cell as a fallback.
Begin by properly qualifying your worksheet references. Use `Dim ws As Worksheet` and assign it using `Set ws = ThisWorkbook.Sheets("Sheet1")` to ensure the macro runs on the correct sheet.
Use the `.Find` method on the target column to locate the exact match. For example: `Set foundCell = ws.Columns(1).Find(What:="TargetBoardType", LookAt:=xlWhole)`.
Implement a `Do...While` loop to check the adjacent column. If the cell matches the target `sysnum`, store the row number and exit the loop. If the cell is blank, store its row number as a fallback but use `.FindNext` to continue searching for an exact match.
Once the correct row is identified, retrieve the value from the minimum or maximum column using a fully qualified reference, such as `ws.Cells(foundRow, minimumColumn).Value`.

Handle VBA and Macros Easily with WPS Spreadsheet
WPS Spreadsheet provides robust, built-in support for VBA macros, allowing you to run, edit, and debug complex lookup scripts seamlessly. It offers a familiar developer environment tailored for efficient data processing without the heavy subscription fees.
- 1. Open your macro workbook: Launch WPS Spreadsheet and open your existing Macro-Enabled Workbook (.xlsm).
- 2. Access the VBA Editor: Navigate to the Developer tab on the top ribbon and click on 'Visual Basic' to open the built-in VBA Editor.
- 3. Insert your VBA code: Right-click on your workbook in the Project Explorer, select 'Insert', and choose 'Module'. Paste your dual-criteria lookup code into the module window.
- 4. Run and debug: Run the macro directly from the editor, or return to the spreadsheet to assign the macro to a custom button for easy access.

Frequently Asked Questions
How can I retrieve the maximum value instead of the minimum value?
To return the maximum value, simply modify the column variable in your retrieval statement. Change `ws.Cells(foundRow, minimumColumn).Value` to point to the index of the column that contains the maximum values.
What is the purpose of using .FindNext in the VBA code?
When using `.Find` to locate matches, `.FindNext` is necessary to continue searching down the column for the subsequent occurrences of your primary criterion without restarting the search from the beginning of the range.
What happens if neither the specific sysnum nor a blank cell is found?
If neither condition is met, the stored row index will remain empty or zero. You should include error handling in your VBA script (e.g., `If foundRow = 0 Then`) to return a default message or alert the user, preventing the macro from crashing.




