SOLVED

# Excel - Return Value based on multiple criteria

Copper Contributor

# 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 if i buy more than 5 or 10 the price will change.

So i need to first look up the Part No. then return the unit cost, but again i need it to skip the unit cost until it falls within a price break quantity. I feel like this is simple but im wrecking my head for a solution.

Thanks in advance for any solution offered

3 Replies

# Re: Excel - Return Value based on multiple criteria

In J4:

=XLOOKUP(I4,FILTER(\$D\$4:\$D\$17,\$A\$4:\$A\$17=H4),FILTER(\$E\$4:\$E\$17,\$A\$4:\$A\$17=H4),"",-1)

You can copy J4 to J6.

best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Excel - Return Value based on multiple criteria

``=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.

# Re: Excel - Return Value based on multiple criteria

Thank you@OliverScheurich

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

Thanks

D

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Excel - Return Value based on multiple criteria

``=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.