Using VLOOKUP for multiple results

[ShoutMercy]

Good afternoon all,

This is a sort of follow up thread to one I post a few months back so I would first like to thank everyone who responded to that thread for their help / assistance / guidance. I once again prostrate myself before you as I look to smarten up the Maintenance Planner I am developing (with your help).

Please see the attached Spreadsheet. Again, it is a mocked up version of the final planner but the basic principles are the same.

As you can see I have 4 work sheets within the Workbook. Basically I am trying to "automate", if that's the right term, the Planner tab within the workbook.

Tabs 1 to 3 show different types of assets and their previous and next service dates. What I have been trying to achieve, unsuccessfully so far, is when a week number is entered into column E2 on the Planner tab it pulls through from all the other tabs the assets that are due service on that particular week. So if I were to type in '11' into column E2 on the Planner tab, then under the Sheet 1 heading on the Planner tab any asset that is due service on the Sheet1 tab gets pulled through, the same for Sheet 2 and Sheet 3.

I have managed to get it to work if its just the one asset that is due in that particular week, the problem I have is getting it to show if there is more than one result expected. I hope that makes sense. Of course feel free to ask any questions and i'll do my utmost to answer them as clear as possible.

Much obliged.

[JvdW]

Hi,

Without looking at your file I can suggest that you have a look at using INDEX & MATCH combo function instead of VLOOKUP, which is very limited.

Good luck

[alansidman]

Here is a VBA solution for you

Please Login or Register  to view this content.

How to install your new code

• Copy the Excel VBA code
• Select the workbook in which you want to store the Excel VBA code
• Press Alt+F11 to open the Visual Basic Editor
• Choose Insert > Module
• Edit > Paste the macro into the module that appeared
• Close the VBEditor
• Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

• Press Alt-F8 to open the macro list
• Select a macro in the list
• Click the Run button

[wk9128]

worksheet name : Planner

cell A2 array formula ,Drag down and across