Finding Value Based On Another Value and Range

Question

So I am having a problem that I am not sure how to solve.

I have a data set in Excel that will be dynamically edited by a user, however the data will be of specific natures; I'm including a screenshot for reference. Referencing the included screenshot, I need a formula in cell H4 that will do the following: If cell G4 is not empty, look at cell B4 in the "Destination" column and see what the value in that cell is, then look in the entire "Destination" range for the same value and if found, look in the "Totes" column in the same row and see if the integer is lower than 500. If it is, look at the "Pallet Locations" column and find the value in the cell that is on the same row, then display that value in cell H4.

Hopefully that makes sense. I'm not really sure what formula to use for this kind of thing or if it is even feasible to do so, but any help would be greatly appreciated.

Thank you.

Answer 1

Another way. Put this formula in cell H4 in your above file.

=IFERROR(IF(MIN(IF(B4:B11=IF(G4<>"",B4,""),E4:E11))<500,INDEX(A4:A11,MATCH(MIN(IF(B4:B11=IF(G4<>"",B4,""),E4:E11)),E4:E11,0)),""),"")

Use Ctrl + Shift + Enter instead of normal Enter

Explanation

If cell G4 is not empty, look at cell B4 in the "Destination" column and see what the value in that cell is

IF(G4<>"",B4,"")

then look in the entire "Destination" range for the same value and if found, look in the "Totes" column in the same row and see if the integer is lower than 500.

For this you need to find the minimum value for N1 in totes column. For that the formula is

MIN(IF(B4:B11="N1",E4:E11))

Lastly if you need to return the value which in the left column, you can use this

INDEX(A4:A11,MATCH("498",E4:E11)),E4:E11,0))

Now simply combine all the above formulas.

Answer 2

Try this in H4 and fill down.

=IF(G4<>TEXT(,), IFERROR(IF(INDEX(E:E, AGGREGATE(15, 7, ROW(4:$11)/(B4:B$11=B4), 2))<500, INDEX(A:A, AGGREGATE(15, 7, ROW(4:$11)/(B4:B$11=B4), 2)), TEXT(,)), TEXT(,)), TEXT(,))