Returning value above searched row

[pharside]

I'm tryin to return a value above the searched row. I've tried a couple options and here is what I have tried:

=HLOOKUP(LEFT(Y4, 4), CHOOSE({1;2},B3:M3,B1:M1), 2, FALSE)

=INDEX(A1:M3,MATCH(LEFT(Y5,4),A1:M3,0),MATCH(LEFT(Y5,4),A1:M3,0))

=VLOOKUP("AISLE", A1:M3, MATCH(LEFT(Y6, 4), A1:M3, 0), FALSE)

I don't have any experience with index, choose, or match as you can likely tell. I am trying to search row 3 for the left 4 of the value in Y4,Y5, and Y6 and then return the content of the cell 2 rows above (column headers).

Any help here would be appreciated.

 

[pharside]

well, right now all of them but that changes depending on who is assigned to what aisle. Often, there is more than one aisle assigned to one person.

Again, B3:M3 all have VALUE() functions in them and your solution works if I manually type in the B3:M3 cells the employee ID number (such as 0050), so I think maybe if I can change the VALUE() function to something else your solution would work. I tried using a TEXT() function, but still didn't work.

Ok, I had an idea to change the LEFT() function to VALUE(LEFT(Y4, 4)) and that worked!

Now, I have a new challenge. So your solution was part of a bigger function that I was trying to build. I have many sets of rows each with their own searched row like B3:M3. My plan was to use the solution you came up with and then use an IFERROR to search the next set of rows. Unfortunately, my function is only returning a correct value when the search criteria matches the first (farthest left) value. Here is my function now:

=LEFT(IF(VALUE(LEFT(Y4,4))>0, IFERROR(OFFSET(INDEX(B3:M3,MATCH(VALUE(LEFT(Y4, 4)),B3:M3,0)),-2,0), IFERROR(OFFSET(INDEX(B9:M9,MATCH(VALUE(LEFT(Y4, 4)),B9:M9,0)),-2,0), IFERROR(OFFSET(INDEX(B15:M15,MATCH(VALUE(LEFT(Y4, 4)),B15:M15,0)),-2,0), IFERROR(OFFSET(INDEX(B21:M21,MATCH(VALUE(LEFT(Y4, 4)),B21:M21,0)),-2,0), IFERROR(OFFSET(INDEX(B27:M27,MATCH(VALUE(LEFT(Y4, 4)),B27:M27,0)),-2,0), IFERROR(OFFSET(INDEX(B33:M33,MATCH(VALUE(LEFT(Y4, 4)),B33:M33,0)),-2,0), IFERROR(OFFSET(INDEX(B39:M39,MATCH(VALUE(LEFT(Y4, 4)),B39:M39,0)),-2,0), IFERROR(OFFSET(INDEX(B45:M45,MATCH(VALUE(LEFT(Y4, 4)),B45:M45,0)),-2,0), IFERROR(OFFSET(INDEX(B51:M51,MATCH(VALUE(LEFT(Y4, 4)),B51:M51,0)),-2,0), "?"))))))))), ""), 1)

 

[pharside]

Ok, I just took a break from this for a couple hours and then came back to realize that I had forgotten to include the "$" in my row locations so when I copied the function down my column, it messed up. Thank you again for all your help, you really helped me work through this one!