Excel worksheet multi-criteria function like Index-Match array to fetch last value of an item looking up by item name and date

0 votes

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))

Sample table. Current calculator with 2 examples. Desired calculator output.

Apr 6, 2023 in Others by Kithuzzz
• 38,010 points
524 views

1 answer to this question.

0 votes

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.

answered Apr 6, 2023 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
1 answer

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

answered Jan 5, 2023 in Others by narikkadan
• 63,700 points
646 views
0 votes
1 answer

Excel formula to check date within this week and last week

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

answered Sep 29, 2022 in Others by narikkadan
• 63,700 points
1,559 views
0 votes
1 answer

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

answered Oct 29, 2022 in Others by narikkadan
• 63,700 points
1,426 views
0 votes
1 answer

MAX function in Excel: is it possible to provide the range by means of variables?

Try this: =MAX(INDEX(A:A,B2):INDEX(A:A,B3)) READ MORE

answered Nov 15, 2022 in Others by narikkadan
• 63,700 points
406 views
0 votes
1 answer

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

answered Nov 17, 2022 in Others by narikkadan
• 63,700 points
552 views
0 votes
1 answer
0 votes
1 answer

Index and Match multiple matches

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

answered Feb 10, 2023 in Others by narikkadan
• 63,700 points
391 views
0 votes
0 answers

MS Excel - SumProduct formula with Loop

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

Feb 18, 2022 in Others by Edureka
• 13,670 points
301 views
0 votes
1 answer

Excel MATCH function is not working on an array but works once directed to the matched value

According to the definition here: support.microsoft.com/en-us/office/… if you do ...READ MORE

answered Nov 17, 2022 in Others by narikkadan
• 63,700 points
747 views
0 votes
1 answer

How to increment row value in an index function in excel?

Try this- =INDEX($F$27:$F$40,COLUMN(A$1)) It will automatically increase the row ...READ MORE

answered Nov 17, 2022 in Others by narikkadan
• 63,700 points
1,417 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP