Matching multiple cells and return certain values

[JakeMann]

Hello,

Need a bit of help wirh a particular formula.

On sheet 'stock report' in column D2 I would like a formula to look at the description in cell A1, match that description on sheet 'Purchase Outstanding', match the week number and if possible match the supplier as well.

Any ideas?

Thanks

[:) Sixthsense :)]

No data is available in Supplier column and what is the output you are expecting for? Please give some more details.

[JakeMann]

The description in A1 on the 'stock report' sheet listed as an outstanding order in column A of the 'Purchase Outstanding' sheet. What I would like to do is to return the quantity on the 'Purchase Outstanding' sheet in column D back onto the 'stock report' page in column D against the applicable week number.

For example if APETREG040550 listed in A1 is due in week 45, then the quanity in column D of the 'Purchase Outstanding' sheet will appear in D7 of the 'stock report' sheet.

[Kevin UK]

Hi JakeMann

Try the attached:

[:) Sixthsense :)]

In D2 cell

Please Login or Register  to view this content.

Drag it down.

[JakeMann]

That's looking good Kevin. Can you briefly explain how that formula is working?

[JakeMann]

Thanks Sixthsense, I'll give it a try...

[Kevin UK]

Hi

Here is a link to site explaining the index and match. INDEX & MATCH All I have done is joined together the lookup values

$A$1&$C2

to look in 2 columns at the same time,

'Purchase Outstanding'!$A$2:$A$92&'Purchase Outstanding'!$F$2:$F$1500

The rest of the formula is for error trapping. If you had Excel 2007, 2010 the formula would be:

Please Login or Register  to view this content.

Hope that makes sense!

[Kevin UK]

Hi JakeMann

Just noticed that the file you uploaded is a .xlsx, you should update your profile. You can use the formula in post# 8, which is wrapped in a IFERROR.

[Kevin UK]

Deleted post

[JakeMann]

Brilliant, thanks Kevin.

[Kevin UK]

They are ARRAY Formula's. CTRL + SHIFT + ENTER