Share via

Finding the last match and returning a value from that row - Index, Max

Anonymous
2022-01-26T17:25:27+00:00

I am looking to create a formula that will return a value in the same row as the last time an item name is listed in a table. The following formula works accurately when there is a match; however it returns 2 for an item that does not appear in the table.

{=INDEX(CountLogTbl,MAX(IF(CountLogTbl[Item]=[@Item],ROW(Items)-MIN(ROW(CountLogTbl[Item]))+1)),4)}

For more clarity, the CountLogTbl contains unit counts of an item in stock, I am trying to find the last time a count was entered for that item in the CountLogTbl. Again, this works when the item name is matched, but if the item is not found it is returning 2.

Can anyone see why it would return 2? Without the index portion, the max formula returns 0 which would be accurate.

Thanks in advance!

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2022-01-26T18:54:27+00:00

    An easy fix is to use a simple conditional:

    =IF(COUNTIF(range, item)=0,"Item is not in the range", - Your old formula here - )

    1 person found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-01-26T19:28:53+00:00

    Good idea, thank you!

    0 comments
  2. Anonymous
    2022-01-26T20:42:48+00:00

    Hi there

    You may try the following formula solution

    =LOOKUP(2,1/(CountLogTbl[Item]=E2),CountLogTbl[Count])

    The following links will provide you with further explanations and ideas.

    https://exceljet.net/formula/lookup-latest-price

    https://exceljet.net/formula/get-value-of-last-non-empty-cell

    I hope this helps you and gives a solution to your problem

    Do let me know if you need more help

    Regards

    Jeovany

    0 comments