Vlookup to achieve multiple results

Resolved · High Priority · Version 365

Thu 1 Aug 2019, 09:13 Delegate Lydia said...

Lydia has attended:
Excel Introduction course

Vlookup to achieve multiple results

I want to create a formula that allows a vlookup returning multiple values, across multiple worksheets. I know both things are individually possible using array formulas but I don't have enough of an understanding of either array formula to work out if there’s a way to combine them.

A few websites give info on how to create an array formula to pull multiple values, this one is clearest and also has a section on looking within multiple sheets. I thought maybe these could be worked together, or the multiple matches extended to run several times across different sheets. Ideally the results would return in a row rather than a column.
https://www.smartsheet.com/advanced-vlookup-multiple-criteria
- How to Use a Lookup to Return Multiple Matches
- How to Look Up Data on Multiple Sheets

Could you advise how this might be achieved?

Thu 1 Aug 2019, 19:37 Trainer Jens said...

RE: Vlookup to achieve multiple results

Hi Lydia,

Thank you for the forum question.

It is a very complicated task and you will need to understand array formulas and complicated lookup and reference functions.

How are the data organised in the source worksheets?
Would you like always to lookup the data in all the source worksheets or do you want to identify, which sheet you want to lookup the data?

Excel like lists and all source data should be in the same list. We shouldn't have a sheet for each month with source data.

If you have organised the data in lists over several sheets and easy solution can be Power Query, but I cannot tell if you can use Power Query, without having more knowledge of how your data are organised.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Tue 3 Sep 2019, 13:37 Delegate Lydia said...

RE: Vlookup to achieve multiple results

Hi Jens,

Thanks so much for your answer and sorry for the late reply. I don't understand array formulas enough to be able to create this myself but wondered if you could help or suggest what to type in?
The data is organised in a table in the source worksheets, with the lookup value on the left and the text I want to return to the right of it in the next column. I'd ideally always lookup the data in all the source worksheets.

Power Query might work but I don't know anything about it - is there any way of me sending you the sheets or a screenshot to show you how it's currently laid out? Unfortunately I would never be able to have all the source data in the same list.

Tue 3 Sep 2019, 21:21 Trainer Jens said...

RE: Vlookup to achieve multiple results

Hi Lydia,

Yes it will be easier if I can see the file.

Please send the file to:

info@stl-training.co.uk

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Training courses

	98.8 % recommend us
	More live stats...

Training information:

Excel tip:

Use the SUBTOTAL function in Excel

You can create subtotals in your spreadsheet using the SUBTOTAL function, which looks like this:

=SUBTOTAL(9,cell:cell)

9 represents the function being used (SUM), followed by the range of cells the function is operating on.

The neat thing about using the Subtotal function is that if you have used it several times in the same column or row, clicking on the AutoSum button at the end of the column or row will make Excel add only the results of cells containing the Subtotal function in that column or row.

View all Excel hints and tips