Skip to main content

Quickly vlookup and return matching values from multiple worksheets

Kutools for Excel

Boosts Excel With 300+
Powerful Features

It may be easy for us to vlookup and return the matching values from current worksheet, sometimes, you may need to return the corresponding values based on the criteria from multiple worksheets. This will be a painful problem for most of us, but, if you have Kutools for Excel, with its LOOKUP Across Multiple Sheets feature, you can solve this job as soon as possible.

Vlookup and return the matching values from multiple worksheets


Vlookup and return the matching values from multiple worksheets

Supposing, you have three worksheets with the original data, and one worksheet with the lookup data to return the corresponding values in Sheet4 based on the given values as below screenshot shown:

To apply this LOOKUP Across Multiple Sheets feature, please do as follows:

1. Click Kutools > Super LOOKUP > LOOKUP Across Multiple Sheets, see screenshot:

2. In the LOOKUP Across Multiple Sheets dialog box, specify the lookup value range and output range under the Lookup values and output range section, see screenshot:

3. Then, click button, in the popped out Data range dialog box, click the sheet tab to go the worksheet which contains the data that you want to use, and then select the data range, see screenshot:

4. And then, click OK button, in the following Add Data range dialog box, specify the key column and return column you need, see screenshot:

5. Then, click OK button to go back to the main dialog box, now, you can see the data range from the first sheet has been added into the Data range list box.

6. Repeat the above step 3-5 to add the data ranges from other worksheets, till all the data ranges from other sheets have been listed into the Data range list box, see screenshot:

7. After finishing the settings, click OK button, a prompt box will pop out to remind you to save this scenario or not. If you want to save this scenario for the next using, please click Yes, otherwise click No to close this prompt box directly.

8. In this case, I will click Yes to save this scenario, and in the Save scenario dialog box, enter a name for the scenario, see screenshot:

9. Then, click OK, all the matching values based on the criteria have been returned across multiple worksheets, see screenshots:

Notes:

1. If the specific value you lookup doesn't exist, a #N/A value will be displayed. To replace this #N/A error value with another text value, you just need to click the Options button. In the popping-up Options dialog, check Replace #N/A error value with a specified value checkbox, and then type the text you need, if you want to display #N/A as empty, just leave blank in the textbox. See screenshot:

2. In the LOOKUP Across Multiple Sheets dialog box:

: Add button: is used to add the data range from other worksheets;

: Edit button: to edit and modify the selected data range as you need;

: Delete button: to delete the selected data range item from the Data range list box;

: Move Up / Move Down: Move the added data ranges up or down. The priority of the lookup is from top to bottom, if there are multiple matching values in multiple worksheets, only the matching values in the topmost data range are returned.

: Save Scenario: To save current added data range as a scenario for next using.

: Open Scenario: Open the scenario dialog box for managing the scenarios, such as delete or open the selected scenario, see screenshot:


Vlookup and return the matching values from multiple worksheets

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!

Productivity Tools Recommended
The following tools can greatly save your time and money, which one is right for you?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: More than 300 Advanced Functions for Excel 2021, 2019, 2016, 2013, 2010, 2007 and Office 365.

Kutools for Excel

The functionality described above is just one of 300 powerful functions of Kutools for Excel.

Designed for Excel(Office) 2021, 2019, 2016, 2013, 2010, 2007 and Office 365. Free download and use for 30 days.

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Comments (3)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
On version 18 - no Super Lookup..!?
This comment was minimized by the moderator on the site
helloI can't find super lookup on my kutools ribbon, please help
This comment was minimized by the moderator on the site
Hey is safe too get tips on excel company hr logo nice to know the experience thank you
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations