in E27:
=INDEX(FILTER(D$17:G$23, (B$17:B$23=C27)*(C$17:C$23=D27)),,XMATCH(B27,D$16:G$16))
Corresponding sample workbook avail. here
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
As you can see I have multiple supplier of multiple items with fluctuating price every week. I want to get price when it match the supplier name and material name and given date.
Accoring to your description, I have a question, will the date criterias and the dates of the source data be different?
If not, they are the same, please check whether Lz-3068's reply is helpful.
You could also try Sumproduct function, =SUMPRODUCT(($C$1:$F$1=$A12)*($A$2:$A$8=$B12)*($B$2:$B$8=$C12),($C$2:$F$8))
If yes, they are may not same, please try following formula.
=INDEX(($C$2:$F$8),MATCH($B12&$C12,$A$2:$A$8&$B$2:$B$8),MATCH(MAX(IF($C$1:$F$1<=$A12,$C$1:$F$1)),$C$1:$F$1))
Any updates, you can let us know.
If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.