Return value of third column based on two column matches in Google Sheets

1
votes

I'm trying to sort a long table so we can analyse the data easier. What kind of data do I have?

  • Column A: Product name
  • Column B: Date (start)
  • Column C: Date (end)
  • Column D: Price

What I need to do is make an overview of all the products and their price on a specific date so I can easily make a chart to show the price changes over time. I can do this via a pivot table, this works. But I also need it as a formula.

Now I'm using: =iferror(index($D$2:$D; match($G3; $A$2:$A; 0); match(H$2; $B$2:$B; 0))) but the problem is that it is only returning the value of one date and not for the other dates (I guess because it is looking for one value and not multiple values)

Is it possible with a combination of match( and index( function?

How can I solve this problem? I hope you guys can help me. Please find a link to a test spreadsheet here: https://docs.google.com/spreadsheets/d/1llvpWCF0VRQMPwDGtQZ0qBiuCIsQBqeJtr7KCq7PBvg/edit?usp=sharing

Thank you in advance!

Example of test spreasheet

1
google-sheetsindexingmatch

1 Answers

2
votes

It is because third argument of INDEX function must point to column number from first argument. In your case there is only one column - $D$2:$D and if third argument returns anything but one it result will be an error and formula returns empty string.

Instead use:

=iferror(index($D$2:$D; match(1;($G3=$A$2:$A)*(H$2=$B$2:$B); 0)))

enter image description here