0

I'm using the below IF function to return a corresponding qty using multiple criteria:

=IF(SKU = SKU Range & Price = Price Range,Qty Range,Qty Range)

Instead of returning the qty values that match the criteria I specified, the function is returning random #'s. I've also tried this function:

=INDEX(SKU Range,MATCH(SKU,QTY Range))

But it only returns the header text as opposed to the qty cell value. Can anyone help with this?

Sheet I want to return qty value using the (SKU/PRICE) criteria

Sheet I'm matching criteria against and want to pull qty value from

Improve this question
5
  • If the combonation of sku and price is unique then you can use SUMIFS: SUMIFS(Qty Range,SKU Range,SKU,Price Range,Price)
    – Scott Craner
    Commented Apr 26, 2019 at 20:39
  • With your formula, it's returning values, but not the correct ones. As an example, below are screenshots of the formula returning the incorrect data: Criteria Sheet: i.sstatic.net/mNuW1.png Array Sheet: i.sstatic.net/RXC09.png
    – Matt Stapleton
    Commented Apr 26, 2019 at 20:54
  • Some of the combos (SKU/PRICE) from the array sheet are unique, but not all.
    – Matt Stapleton
    Commented Apr 26, 2019 at 21:02
  • How do you know which one to pick if/when they are not unique?
    – cybernetic.nomad
    Commented Apr 27, 2019 at 10:17
  • @MattStapleton, you mean to say if Formula finds SKU Name & Price of Sheet 1 into Sheet 2 has to return the Quantity ?
    – Rajesh Sinha
    Commented Apr 28, 2019 at 7:10

1 Answer 1

Reset to default
0

I would like to suggest two possible solutions. First one finds single Quantity based on SKU and Price and Second method returns multiple Quantities when match with SKU & Price between Sheets.

enter image description here

Method 1:

  • Enter SKU & Price in Sheet1 cell A10 & B10 and enter this Array (CSE) Formula in Cell C10, finish with Ctrl+Shift+Enter.

    {=IFERROR(INDEX(Sheet2!$C$2:$C$8,MATCH(1,(A$10=Sheet2!$A$2:$A$8)*(B$10=Sheet2!$B$2:$B$8),0)),"")}

Method 2:

  • Enter SKU & Price in Sheet1 cell A15 & B15 and enter this Array (CSE) Formula in Cell C15, finish with Ctrl+Shift+Enter & fill it down.

    {=IFERROR(INDEX(Sheet2!$C$2:$C$8, SMALL(IF(COUNTIF($A$15, Sheet2!$A$2:$A$8)*COUNTIF($B$15, Sheet2!$B$2:$B$8), ROW(Sheet2!$A$2:$A$8)-MIN(ROW(Sheet2!$A$2:$A$8))+1), ROW(A1)), COLUMN(A1)),"")}

N.B.

Adjust Cell references in both Formulas as needed.

Improve this answer
2
  • This worked ty!
    – Matt Stapleton
    Commented Apr 29, 2019 at 13:56
  • Glad to help you @MattStapleton,, keep asking ☺
    – Rajesh Sinha
    Commented May 1, 2019 at 8:02

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .