INDEX MATCH - Find the latest entry

[MonkeyFace28]

Hi y'all

Hope everyone is well and looking forward to Christmas!! YAY!!

I have a quick question, I have done a bit of searching for an answer to my question but couldn't find anything that seemed to work when I applied it to my sheet.

I have sheet (OPEN_GOs) containing order numbers column B, I need to return that latest Invoice for said order number. Here is my index match formula.

=IFERROR(INDEX(report14!$E$2:$E$967,MATCH(OPEN_GOs!B2,report14!$M$2:$M$3299,0)),"NO INVOICE")

There is a date column (report14 - column B) that could be used to determine a unique reference, but I can't think how to use it?

Thanks in advance for reading and your help

Steph xx

 

[jorismoerings]

Hi,

Don't think @Aladin Akyurek mend any wrong doing towards you but is just trying to explain how forum members can help you in the easiest and best way possible.

Anyway...

Your question is a clarification of your initial ask. The formula i provided is assuming there just 1 invoice per date, subsequently the latest date will hold the latest invoice.
By showing your data we can see there can be multiple invoice for the same order on the same day.

I think this could be helpful:
Assuming the reference to the invoice will be numerical and a higher number refers to a later invoice, an array formula so to be confirmed with Control+shift+enter.

=IFERROR(INDEX(report14!$E$2:$E$3299,MATCH(MAX((report14!$M$2:$M$3299=OPEN_GOs!B33)*(report14!$E$2:$E$3299)*(report14!$B$2:$B$3299)),(report14!$B$2:$B$3299*report14!$E$2:$E$3299),0)),"Not Invoiced Yet")

 

[MonkeyFace28]

thank you jorismoerings, I haven't had chance to check this formula yet. I will try.

I don't think he did either, I think I was feeling a bit sensitive that day, I did try to edit it but it had been more than 10mins and would have had to ask admin and blah blah.

Will let you know if it works thanks a mill

 

[MonkeyFace28]

I think this could be helpful:
Assuming the reference to the invoice will be numerical and a higher number refers to a later invoice, an array formula so to be confirmed with Control+shift+enter.

=IFERROR(INDEX(report14!$E$2:$E$3299,MATCH(MAX((report14!$M$2:$M$3299=OPEN_GOs!B33)*(report14!$E$2:$E$3299)*(report14!$B$2:$B$3299)),(report14!$B$2:$B$3299*report14!$E$2:$E$3299),0)),"Not Invoiced Yet")

@jorismoerings

I have put the formula in but I'm getting a "0" where there are errors? I have doubled check the logic and it makes sense, I mean its an IFERROR, there's two parts to the argument! the value and the result if the value is true?

I think I'm going to start again with this spreadsheet as now some of my other formulas aren't working. I shall also do some digging to see how I can upload a data file so people can experiment if needed

EDIT - just hit enter (by mistake) so without the { } and the output was "Not Invoiced Yet" could it be to do with the array?

 

[jorismoerings]

Hi,

Could be but without an ability to work with actual data it's not something i can answer.
Are you able to upload your file (desensitized!) to a file share service like Box, Dropbox, etc. and share the link in a post so we can work with it without retyping everything and second will eliminate any assuming ogf how your file looks like.

 

[MonkeyFace28]

https://www.dropbox.com/s/z4mf56zyywn29io/MrExcelVersion_Live_GO_files.xlsx?dl=0

I think I've done it

 

[jorismoerings]

Hi,

The 0 values are created by the used arrays in the formula, particularly the MAX function part.
Change the formula to:
=IFERROR(INDEX(report14!$E$2:$E$4000,MATCH(IF(MAX((report14!$M$2:$M$4000=OPEN_GOs!B2)*(report14!$E$2:$E$4000)*(report14!$B$2:$B$4000))=0,NA(),MAX((report14!$M$2:$M$4000=OPEN_GOs!B2)*(report14!$E$2:$E$4000)*(report14!$B$2:$B$4000))),(report14!$B$2:$B$4000*report14!$E$2:$E$4000),0)),"Not Invoiced Yet")

This should do the trick.

 

[MonkeyFace28]

Hi,

The 0 values are created by the used arrays in the formula, particularly the MAX function part.
Change the formula to:
=IFERROR(INDEX(report14!$E$2:$E$4000,MATCH(IF(MAX((report14!$M$2:$M$4000=OPEN_GOs!B2)*(report14!$E$2:$E$4000)*(report14!$B$2:$B$4000))=0,NA(),MAX((report14!$M$2:$M$4000=OPEN_GOs!B2)*(report14!$E$2:$E$4000)*(report14!$B$2:$B$4000))),(report14!$B$2:$B$4000*report14!$E$2:$E$4000),0)),"Not Invoiced Yet")

This should do the trick.

Hey,

When I paste the formula in to the cell, do the Ctrl+Shift+Enter a file window pops up asking me to update values 4. I think I'm happy with it showing a "0" to be honest, the formula is working and the thing that matters most is the correct invoice number showing so I think you have done more than enough to help me thank you soo much for all your help x

Just put a conditional format in there so I can't see the "0" so I'm a very happy bunny now, thanks again!