Finding Second Occurrence if Index Match Returns -

Good afternoon,

I am in need of a formula that searches a table for a second occurrence of criteria if the first occurrence returns a -.

Example

Item State Qty
A NC -
A NC 10
B SC -

In this instance, I would want to return qty 10 for Item A State NC. However, if item and state only existed once (i.e. Item B State SC) and had a - in the Quantity field return a -.

Thanks.

Beaten to it!

All,

Thanks for the response. This resolves my issue when there are 2 or more occurrences; however, for the formula provided for Item B State SC this returns a 0 instead of a "-". Anyway to return an "-" instead of 0 if that is what is listed? There will be instances where there may be a 0 in my actual data which has a different value than the "-".

Thanks.

Try

=IFERROR(INDEX($C$1:$C$50,SMALL(IF((A1:A50="B")*($B$1:$B$50="SC")*(ISNUMBER($C$1:$C$50)),ROW($A$1:$A$50)-ROW($A$1)+1),1)),"-")

Enter with Ctrl+Shift+Enter

Put Item/state in cells as per Tony's formula

Tony,

Correct, the dashes are not formatted 0's. In this case "-" indicates no service. If there is a number (0 included) that should be returned over the hyphen. I have attached an example of what I am looking for. Hope this helps.

Try

=IFERROR(INDEX(Data!$F$1:$F$50,SMALL(IF((Data!$A$1:$A$50=A2)*(Data!$B$1:$B$50=B2)*(ISNUMBER(Data!$F$1:$F$50)),ROW($A$1:$A$50)-ROW($A$1)+1),1)),"-")

Enter with Ctrl+Shift+Enter

Match on City & Country (??)