Help with Index and Match Function I Think

[DBExcelDB]

Hello,

I am trying to figure out how to match 4 cells of data and then return a different cell value.

Hopefully the attached spreadsheet template will help me explain a little better. I want to generate data in L4 in ‘MRP’ that if the cells C3 & K2 in the sheet titled ‘MRP’ match those in column A & B in sheet titles ‘Production requirements’ then it will return the figure in column C.

So the figure in L4 would be 14.098.

I’m struggling to work out how to do this and which function I would use so any help would be gratefully received.

Thank you

[KOKOSEK]

14.098 can be in L10 if I am correctly read your needs.
In L4 can't be 14.098 because for 04/11/2019 14.098 is assigned into CR007 not CR010.
Am I right?

Try into K4:

Please Login or Register  to view this content.

and drag it right as long as needed.

also you can copy this into K10 and also drag it right.

[Pete_UK]

Your cell references are a bit out - put this formula in L10:

=SUMIFS('Production Requirements'!$C:$C,'Production Requirements'!$A:$A,$C10,'Production Requirements'!$B:$B,L$2)

then you can copy it across. You can also copy it to L4 (where it produces 0), and then copy it across.

Hope this helps.

Pete

[Glenn Kennedy]

While I am looking at your file, please take a moment to update your profile. Clearly you are no longer using Excel 2003 (.xlsx attached).

[menem]

Please test my formula.

When I use this formula in L4 it give me 0 value.

Please Login or Register  to view this content.

But when I use this formula in L10 it give me 14.0980

Please Login or Register  to view this content.

Regards.

[Glenn Kennedy]

In K4, copied across:

=IFERROR(INDEX('Production Requirements'!$C$2:$C$27,MATCH(1,INDEX(('Production Requirements'!$A$2:$A$27='MRP '!$C3)*('Production Requirements'!$B$2:$B$27='MRP '!K$2),0),0)),"")

Your expected answer in L4 is incorrect, as it relates to CROO7, and appears in L11, not L4.

[DBExcelDB]

Thanks for the replies really helpful. Both the SUMIF and the INDEX/MATCH solutions WORK. Just trying to figure out now which is the best one yo use.

Thank you everyone for your help.

[Pete_UK]

Glad to help.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete