Quickly vlookup and return matching values from multiple worksheets

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