VBA & Macro Problems

How to Create a VBA Macro to Find a Result Using Two Input Values in Excel

Rana Garcia Oct 20, 2026 869 views

Question details

The user needs to create an Excel VBA macro that prompts for two values (temperature and KCl percentage), locates the matching row and column in a data table, and returns the intersecting result while properly handling missing matches.

How to Create a VBA Macro to Find a Result Using Two Input Values

Product: Excel
Device & OS: not provided
Scenario: Performing a two-way lookup in a 2D table using VBA prompts instead of worksheet formulas.
Observed behavior: Using nested ElseIf chains is returning incorrect values (e.g., 0 instead of 2.92), and standard VLOOKUP methods are throwing 'Unable to get the VLookup property of the WorksheetFunction class' errors when matches fail.

Before you start

Ensure your data is organized as a proper two-dimensional table with distinct row headers (e.g., temperatures) and column headers (e.g., KCl percentages) on your active worksheet, and verify that the Developer tab is enabled to access the VBA Editor.

Solution 1Recommended

Use Application.WorksheetFunction with MATCH and INDEX

Using built-in Excel functions via VBA is the most efficient and reliable way to perform a two-way lookup without relying on complex, error-prone ElseIf chains.

This approach mimics the standard worksheet combination of INDEX and MATCH, which is significantly more dynamic than hard-coding conditional statements. It leverages VBA's WorksheetFunction class to calculate the intersection on the fly.

Open the VBA Editor

Press ALT + F11 to launch the Visual Basic Editor. Go to Insert > Module to create a new module for your macro.

Prompt the user for inputs

Declare your variables and use the InputBox function to ask the user for the Temperature and KCl percentage. For example: `tempInput = InputBox("Enter Temperature:")`.

Locate row and column indexes

Use `Application.WorksheetFunction.Match` to find the exact row for the temperature and the column for the KCl percentage within their respective header ranges.

Retrieve the intersecting result

Pass the found row and column numbers into the `Cells(row, col)` property or use `Application.WorksheetFunction.Index` to extract the corresponding table value.

Add error handling

Wrap your matching logic in an `On Error GoTo` block. This prevents the script from crashing if the user enters a value that doesn't exist in the table, allowing you to display a custom 'Match not found' message instead.

Use Application.WorksheetFunction with MATCH and INDEX

Pro Tip: Always set the third argument of the MATCH function to 0 (`Match(lookup_value, lookup_array, 0)`) in your VBA code to enforce an exact match.

Solution 2

Locate Values Using the VBA Range.Find Method

The Range.Find method searches directly within the worksheet cells and is highly effective for custom lookup scenarios or varying data layouts.

Advanced Macros in WPS Spreadsheet

Execute Macros and Complex Lookups Effortlessly in WPS Spreadsheet

WPS Spreadsheet provides powerful support for advanced formulas and VBA macros, allowing you to seamlessly execute 2D lookups, automate data entry, and validate user inputs without modifying your existing Excel scripts.

1. Install WPS Office: Download and install WPS Office, then open your macro-enabled spreadsheet (.xlsm) in WPS Spreadsheet.
2. Access the Developer Tools: Navigate to the 'Tools' tab on the ribbon and click on 'Developer' to access the macro environment.
3. Edit Your Macro: Open the VBA Editor from the Developer tab, paste your two-way lookup code (using Find or INDEX/MATCH), and run the macro directly.
4. Save with Compatibility: Save your workbook in the standard .xlsm format, ensuring full compatibility when sharing the file with Microsoft Excel users.

Fully compatible with Microsoft Excel's VBA and macro scripts (.xlsm).Native support for complex array formulas like INDEX and MATCH.Lightweight installation with an intuitive, tabbed user interface.Free to use for everyday spreadsheet automation and data analysis.

Frequently Asked Questions

Why do I get the 'Unable to get the VLookup property' error in VBA?

This runtime error occurs when `Application.WorksheetFunction.VLookup` cannot find an exact match for your lookup value. Ensure the value exists in the first column of the specified range, the return-column index is strictly within the range bounds, and the final argument is set to `False` for exact matching.

Why should I avoid using ElseIf statements for a two-way table lookup?

Writing ElseIf statements for a two-dimensional data table requires hardcoding every possible row and column combination. This makes the macro unnecessarily long, difficult to maintain, and highly prone to logic errors (such as returning 0 if a specific intersection condition is slightly mistyped).

How can I validate user input before running the lookup macro?

You can use a `Do While` loop combined with the `IsNumeric` function to evaluate the InputBox response. This ensures the user types a valid numerical value before the VBA script attempts to pass the variable into the Match or Find functions.

What formula can I use directly in the worksheet instead of a VBA macro?

You can use the standard combination `=INDEX(ReturnRange, MATCH(RowInput, RowRange, 0), MATCH(ColInput, ColRange, 0))`. This accomplishes the exact same two-way lookup dynamically without requiring VBA macro execution.

Excel & Data Problems