Looking to see if possible

[pstubbs90]

I am trying to return the text in a column, when the value in another column is greater than 0 for any entry matching a value. From below I need to write the Loc of each bin into the cell with ??.

[TABLE="width: 200"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Blocked[/TD]
[TD]Loc[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]123[/TD]
[TD]??[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Blocked[/TD]
[TD]Loc[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]100[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]20[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]3[/TD]
[TD]S[/TD]
[/TR]
</tbody>[/TABLE]

 

[sgroath]

Assuming:
your top section is in cells A1:C2 with the ?? in cell C2

and

your lower data in A4:C10 (I added another row to hold the combo od Item being X and Blocked being 123

try the following array formula in C2 (put in the following and then hit Ctrl+Shift+Enter instead of just Enterto make it an array formula:

=IFERROR(INDEX(A5:C10,MATCH(A2&B2,A5:A10&B5:B10,0),3),"Not Found")

Once you hit Ctrl+Shift+Enter, the formula should look like the following (do NOT type in the curly braces)
{=IFERROR(INDEX(A5:C10,MATCH(A2&B2,A5:A10&B5:B10,0),3),"Not Found")}

 

[pstubbs90]

Unfortunately I do not think what I am looking for is possible without using VBA. The output of cell with ?? I want to read as S, H. With index match and vlookup I can get one value but I want to be able to capture both locations.

 

[Gerald Higgins]

Is it possible there could be more than 2 locations, or is it only ever S and H ?

 

[pstubbs90]

Only the two locations as of now.

 

[Gerald Higgins]

In that case, is it POSSIBLE that the answer might sometimes be something other than

S, H

?

 

[pstubbs90]

There are a total of 4 locations, only 2 will ever have blocked material. Right now I am separating the locations with blocked qty so only the 2 locations in the data set.