Function Problems

Excel Alternatives to VLOOKUP for Duplicate Values in Multiple Columns

Maira Mehtab Sep 20, 2026 868 views

Question details

The user needs a method to look up and return associated values when the lookup numbers appear across multiple columns, seeking an alternative to the traditional VLOOKUP function.

Product: Spreadsheet
Device & OS: not provided
Scenario: Searching for specific values that are distributed across a multi-column range and extracting all corresponding associated data, including duplicates.
Observed behavior: Standard VLOOKUP is inadequate because it only searches the first column of a given range and returns only the very first match it encounters.

Before you start

Ensure your spreadsheet software supports modern dynamic array functions such as FILTER and TOCOL, which are required for advanced multi-column extraction without complex VBA code.

Solution 1Recommended

Use Dynamic-Array Functions (FILTER, TOCOL, IF)

Combine modern array functions to flatten your multi-column data and filter out all matching associated values natively.

By nesting TOCOL inside a FILTER function alongside an IF statement, you can transform a multi-column table into a single logical array. This allows the spreadsheet to evaluate every cell across the columns and spill out all corresponding values.

Identify your data ranges

Locate the multi-column range containing your lookup values and the specific single-column range of associated values you want to return.

Flatten the multi-column array

Click on an empty cell where you want the results to appear and type =TOCOL(your_multi_column_range) to flatten the lookup data into a single column temporarily in the formula's memory.

Apply the FILTER function

Wrap the TOCOL function inside a FILTER function. Use an IF statement as the include criteria to match against your specific lookup number. For example: =FILTER(return_array, TOCOL(lookup_array)=target_value).

Execute the formula

Press Enter. The dynamic array will automatically spill the results down the column, returning all associated values including duplicates.

Boolean Logic Verification: In these array formulas, a zero represents FALSE. If your results aren't filtering correctly, double-check your TRUE/FALSE logic within the IF criteria.

Solution 2

Restructure Data to a Single Lookup Column

If dynamic arrays are not available in your version, reorganize your data so that all lookup values are consolidated into one column.

Powerful Spreadsheet Solution

Handle Complex Lookups Easily with WPS Spreadsheet

WPS Spreadsheet offers a comprehensive set of functions, including advanced lookup and dynamic array formulas, allowing you to easily manage multi-column searches and extract duplicate values without hassle.

1. Open your data file: Launch WPS Office and open your .xlsx file in the WPS Spreadsheet application.
2. Select the output cell: Click on the specific cell where you want the extracted duplicate values to begin spilling.
3. Insert the array formula: Navigate to the Formulas tab, select Insert Function, and input your combined FILTER and TOCOL formula to process the multi-column array.
4. Retrieve matches: Press Enter to execute the formula and instantly retrieve all matching records from your multi-column lookup range.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Natively supports advanced lookup and dynamic array functions to replace complex VLOOKUP scenarios.Free to use with a lightweight, tabbed, and intuitive interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the standard VLOOKUP work for multiple columns?

VLOOKUP is strictly designed to search for a lookup value only in the first (leftmost) column of a specified table array. If your search values are distributed across multiple columns, VLOOKUP cannot scan them simultaneously.

What does the TOCOL function do in this scenario?

The TOCOL function takes a two-dimensional array or a multi-column range of cells and returns them as a single one-dimensional column. This flattens the data, making it possible for functions like FILTER to search through data that was originally spread out.

Can I use INDEX and MATCH to return duplicates from multiple columns?

While INDEX and MATCH are more flexible than VLOOKUP, a standard setup still inherently searches a single row or column and stops at the first match. To search multiple columns and return multiple duplicate matches simultaneously, dynamic array functions like FILTER are required.

How does the FILTER function handle duplicate lookup matches?

The FILTER function is designed to extract all records that meet a specified logical criteria. If a lookup value appears multiple times within the targeted array, FILTER will automatically return an array containing every corresponding match, handling the duplicates effortlessly.

Excel & Data Problems