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 ??.

Item	Blocked	Loc
X	123	??

<tbody>
</tbody>

Item	Blocked	Loc
A	1	S
X	100	H
B	2	H
X	20	H
X	3	S

<tbody>
</tbody>

 

[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.