I'm providing an example table with a list of goods and their current prices. My Index-Match array method allows you to check up a product's pricing for a specific month. But I'm looking for a function that returns the most recent value entered for a product. So, the function should look up the product name, then use the date column to find the most recent date on which the product has a record, then retrieve the price for that row. You can't merely use the most recent date because not all goods are updated every month. The expiration dates for different products will vary. (For instance, only product #3333 in the current calculator has an entry for 5/1/22, therefore the other two display #N/A. That's okay.)

I'm using this formula in current calculator, cell F5 (as in image):

=INDEX(\$C\$4:\$C\$12, MATCH(1,(E5=\$B\$4:\$B\$12)*(\$G\$4=\$A\$4:\$A\$12),0))

Apr 6, 2023 in Others 445 views

## 1 answer to this question.

Use:

```=SUMIFS(C:C,B:B,E12,A:A,MAXIFS(A:A,B:B,E12))
```

It will return the value at the last date where the product number matches.

• 63,420 points

## Can an Excel xll function indicate that the return value should be displayed as a date and not a number?

To my knowledge, the only method to ...READ MORE

## Excel formula to check date within this week and last week

Assuming the first date in A2 try this formula ...READ MORE

## How to hide and unhide the columns of an excel sheet using asp.net

Use this. For Row: worksheet_sub.Row(i).Height = 0; For Column: ...READ MORE

## Excel - IF combined with Index Match

Try: =IFERROR(IF(ISNUMBER(O2),VLOOKUP(A2,Sheet2!\$C\$2:\$E\$10,3,FALSE),""),"") =IFERROR(IF(ISNUMBER(O2),INDEX(Sheet2!\$E\$2:\$E\$10,MATCH(A2,Sheet2!\$C\$2:\$C\$10,0)),""),"") where the first "" means that O2 is not a number, ...READ MORE

## INDEX MATCH formula in Excel returning incorrect results in some cells and correct results in others

Step through the formula that produces the ...READ MORE

## Index and Match multiple matches

You can try the following (formula 1) ...READ MORE

## MS Excel - SumProduct formula with Loop

1 I have 4 arrays of data where ...READ MORE