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,

Try something like this:

=IFERROR(INDEX(report14!$E$2:$E$3299,MATCH(MAX(--(Report14!$M$2:$M$3299=OPEN_Gos!B2)*(Report14!$B$2:$B$3299)),Report14!$B$2:$B$3299,0)),"NO INVOICE")



It's an array formula so and confirm with CTRL+Shft+Enter

 

[MonkeyFace28]

Hi,

Try something like this:

=IFERROR(INDEX(report14!$E$2:$E$3299,MATCH(MAX(--(Report14!$M$2:$M$3299=OPEN_Gos!B2)*(Report14!$B$2:$B$3299)),Report14!$B$2:$B$3299,0)),"NO INVOICE")



It's an array formula so and confirm with CTRL+Shft+Enter

AWWW yay, Heel erg bedankt xx

 

[Aladin Akyurek]

Your description (which is incomplete) does no align well with the formula you posted...

The formula moreover references two unequally sized ranges.

 

[MonkeyFace28]

Your description (which is incomplete) does no align well with the formula you posted...

The formula moreover references two unequally sized ranges.

Yes, apologies, I saw that after. Thanks for pointing that out.

 

[MonkeyFace28]

Hello all, have the same issue... I've pasted a new report in my raw data sheet and it's not showing the latest invoice in the output. Is there a way I can upload the data so y'all can see what I mean?

thanks in advance

 

[MonkeyFace28]

this is a snapshot of the sheet...

this is the report I'm pasting the new data download into, that should be populating the "open GO's" sheet.

you can see here on lines 10 and 11 that it has the same ref in column M and different invoice numbers in column E - all I need is that highest number returned. so in this case it would be "50313" but the formula I'm using isn't doing that...

here is the formula :
{=IFERROR(INDEX(report14!$E$2:$E$3299,MATCH(MAX(--(report14!$M$2:$M$3299=OPEN_GOs!B33)*(report14!$B$2:$B$3299)),report14!$B$2:$B$3299,0)),"Not Invoiced Yet")}

thanks in advance for any help!

 

[Aladin Akyurek]

1. A picture requires retyping if one has to experiment for a solution: An unreasonable implicit demand.

2. Providing a non-working formula as a substitute for problem description becomes more often not misleading.

3. Here is a generic attempt... Control+shift+enter, not just enter:

=MAX(IF(report14!$M$2:$M$3299=condition,report14!$E$2:$E$3299))

Here condition stands for whatever must hold for the range in column M... If you have MAXIFS on your system, just enter instead:

=MAXIFS(report14!$E$2:$E$3299,report14!$M$2:$M$3299,condition)

 

[MonkeyFace28]

1. A picture requires retyping if one has to experiment for a solution: An unreasonable implicit demand.

2. Providing a non-working formula as a substitute for problem description becomes more often not misleading.

3. Here is a generic attempt... Control+shift+enter, not just enter:

=MAX(IF(report14!$M$2:$M$3299=condition,report14!$E$2:$E$3299))

Here condition stands for whatever must hold for the range in column M... If you have MAXIFS on your system, just enter instead:

=MAXIFS(report14!$E$2:$E$3299,report14!$M$2:$M$3299,condition)

1. I did ask if anyone knew how to add the spreadsheet to the post... no one answered. Also, I was not demanding anything!! I just thought seeing the data might help! I would never expect anyone thinking of helping me to type out data from scratch.

2. I thought I explained what wasn't working? - the formula isn't showing the latest invoice number? it isn't even showing an invoice that is related to the GO number. I thought I had also said what I needed it to do? sorry I am really not sure how I could have been clearer.

3. thank you for your "generic attempt"

4. I think if you want to help someone in the future, maybe just help them... or NOT! you don't need to be rude or have a go at them for not knowing how to do something. It's people posting like yourself that makes people like me scared to post things on sites like these for fear of being laughed at or ridiculed.

 

[Aladin Akyurek]

Do either 3 or 4 of post #8 not work for you?

Here is some info how to provide Excel readable samples?