I have the following sample data.

```Date         Category   Price   Quantity
02-01-2019  BASE_Y-20   279 1
02-01-2019  BASE_Y-21   271.25  0
03-01-2019  BASE_Y-20   276.5   2
03-01-2019  BASE_Y-21   266.5   0
04-01-2019  BASE_Y-20   272.88  14
04-01-2019  BASE_Y-21   266.5   1
07-01-2019  BASE_Y-20   270.48  29
07-01-2019  BASE_Y-21   262.75  0
08-01-2019  BASE_Y-20   270 4
08-01-2019  BASE_Y-21   264 0
09-01-2019  BASE_Y-20   270.06  31
09-01-2019  BASE_Y-21   262.85  0

```

What dynamic formula can I use to retrieve the most recent 5 prices for category BASE Y-20? The difficult element is that, if 5 values are missing, the formula must return any prices that are available. (For example, The values 270.06, 270, 270.48, 272.88, and 276.5 must be returned for the given data. If we only had the first row, it would have to return 279)

I used some products. Naturally, that provides the corresponding prices. To obtain the last 5 data, offset is available. But there is no way to see the most recent dynamically updated prices for the top 5 categories.

Feb 5, 2023 in Others 192 views

## 1 answer to this question.

Try:

The formula in F3:

`=TAKE(SORT(FILTER(A:C,B:B=F1),1),-F2,-1)`
• 63,420 points

## How to automatically assign a color to the maximum and minimum values in a set of selected cells in Excel?

See Conditional Formatting, which may be accessed ...READ MORE

## Best way to get average values within a specific set of time(say, date or year) using MySQL or Rails

I don't think post-process is what you ...READ MORE

## Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

## Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

## IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE