VBA & Macro Problems

How to Perform a VBA Exact Match Lookup with an Optional Second Criterion

Anum MushtaqAnum Mushtaq Oct 28, 2026 869 views

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.

VBA Exact Match Lookup with an Optional Second Criterion
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.
Before you start

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.

Solution 1Recommended

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.

1
Declare variables and define the worksheet

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.

2
Search for the primary criterion

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)`.

3
Evaluate the secondary criterion inside a loop

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.

4
Return the target value

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`.

Implement a VBA Lookup with Fallback Logic
Fully Qualify Your References: Always use ws.Cells instead of an unqualified Cells reference to prevent runtime errors, especially when the code might be triggered while a different sheet is active.
Advanced Macro Support in WPS Office

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. 1. Open your macro workbook: Launch WPS Spreadsheet and open your existing Macro-Enabled Workbook (.xlsm).
  2. 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. 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. 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.
Full compatibility with Microsoft Excel VBA scripts and .xlsm files.Built-in Visual Basic Editor for writing and debugging custom macros.Lightweight architecture that processes heavy automation tasks quickly.Free to use with a user-friendly, tabbed interface.
microsoft office alternative - wps office

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.