Excel - Return Value based on multiple criteria

I am trying to work out how to do a lookup based on required quantity. I have a forecast which shows me how many items i need to buy per item, some of the items have price breaks from the vendors so...

Formulas and Functions

OliverScheurich
May 11, 2023
DBolton10
=INDEX($F$4:$F$17,MATCH(1,($A$4:$A$17=I4)*($D$4:$D$17<=J4)*($E$4:$E$17>=J4),0))
You can try this formula along with a helper column. In the example the helper column is "Qty to" column E. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

OliverScheurich

Gold Contributor

May 11, 2023

DBolton10

=INDEX($F$4:$F$17,MATCH(1,($A$4:$A$17=I4)*($D$4:$D$17<=J4)*($E$4:$E$17>=J4),0))

You can try this formula along with a helper column. In the example the helper column is "Qty to" column E. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

DBolton10

Copper Contributor

May 12, 2023

Thank youOliverScheurich

This worked well, i really need to study up on index/match. i do everything else but never mastered this.

Thanks

Forum Discussion

Excel - Return Value based on multiple criteria

Resources