Mod to current IFERROR INDEX MATCH Formula

Hi Folks

Currently have this formula:

Formula:

Please Login or Register  to view this content.

As it is it picks up the first (oldest) match but I would like it to pick up the latest match.

I do not want to have to alter the file it exports from in any way as it is auto updated from a program.

So needing it to do the same job but pick up the latest entry not the first as it does now.

Cheers
hammer

Hi Hammer,

Match function will always retrieve first find value and return number where particular Lookup value based.

If you can upload your sample workbook with expected result it will be good to get solution easily.

As already pointed out, MATCH (which the INDEX is based on) will find the very 1st "match" then stop looking.

If you are working with dates, and the last entry is the latest date, then you probably need to use LARGE to ID the latest date that matches your criteria, but I cannot put something together for you without seeing a sample of what you are working with,

can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

see if this works

Formula:

Please Login or Register  to view this content.

ps its case sensitive
also change 100 to something larger to suit

Just hazarding a guess ... array entered.

=IFERROR(INDEX('D:\[TIMEWATCH EXPORT HISTORY.csv]DATA'!$D:$D,INDEX(MATCH(2,1/($J2='D:\[TIMEWATCH EXPORT HISTORY.csv]DATA'!$K:$K),1),)),0)

Without data though it's a shot in the dark.

Whilst I was reading up on how to make formulas case sensitive I gave FlameRetired's solution a try-
It works just peachy! Also, just for interest, it works without being array entered.

Who needs examples with you people around?
Stirling effort again Folks!

Cheers
hammer

hammer,

Thanks for the feedback, and yeah I guess with the INDEX wrapped around the MATCH it wouldn't need array entry. Thanks for pointing that out. I missed that one.

just for reference
to make any formula not case sensitive wrap it with upper (or lower)