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?
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
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.
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
