Return Value In A Shared Workbook

[Stenner]

Hi All

I use a shared workbook for my job in supply chain, with a shared workbook I believe I can't use array formulas all to well so I am stuck on how to get a return value.

I have attached a mock workbook and I will explain what I am after.

In cell E5 on the tab 'Dispatch' I want the return of an In Stock Date which is found in column N on the tab 'Dates'. The criteria of order number would need to match for this to work.
I tried a nested IF but it would mean that both data on each tab would need to match cell for cell in order. As I will be dragging the formula down this wouldn't work.

Any help is appreciated!

Regards

Aarron

[FDibbins]

Try this...
=INDEX(Dates!$N:$N,MATCH(Dispatch!C5,Dates!$D:$D,0))

[Stenner]

It works perfect thank you!

Could I ask an additional question?

How does the formula work?
Here is my interpretation of it.
The formula works backwards so it looks for the match first being the order number then returns the cell in the same row that is within the index.

Does that sound about right?

Regards

Aarron

[FDibbins]

Yup, pretty much. Most complex formulas work from the inside out, although some can jump around a bit.

In this case, I am using INDEX, which needs a range (in this case a single column), and a row to pull from. So, to find the row we need, I use MATCH which returns a row number - just what we need here

Happy to help and thanks for the rep