Complex Vlookup Formula

Hi All,

I need a some help with a complex formula for the atatched spreadsheet in short I need two things done if possible.

1. Ordersheet : - Based on information from A2 it will match columns B,D & E from Master Data columns C,D & E the idea is that Master Data is the sheet where the information is changed and kept up to date. The order sheet is where the kitchen orders the stock. Cell A2 is the drop down field for the supplier and the information is then brought up in the relevant columns.

2. Purchase Orders – columns B:E pulls the information from the order sheet (qty) if there is a value more than 1.

Please state the nature of your problem in your question. I won't open a macro-enabled file without any explanation of what you are trying to achieve.

This array formula will do the job (adjusting the columns in the INDEX part appropriately)

=IFERROR(INDEX('Master Data'!C:C,SMALL(IF('Master Data'!$A$2:$A$69='Order Sheet'!$A$2,ROW('Master Data'!$A$2:$A$69)),ROWS($1:1))),"")

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

A similar formula on the other sheet completes the job.