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]

What about the HLOOKUP and CHOOSE combination? I am not sure why I can't get it to work, but that seems like a viable option.

 

[pharside]

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]AISLE

[/TD]
[TD]_
[/TD]
[TD]A1
[/TD]
[TD]_
[/TD]
[TD]A2
[/TD]
[TD]_
[/TD]
[TD]A3
[/TD]
[TD]_
[/TD]
[TD]A4
[/TD]
[TD]_
[/TD]
[TD]A5
[/TD]
[TD]_
[/TD]
[TD]B1
[/TD]
[TD]_
[/TD]
[TD]B2
[/TD]
[TD]_
[/TD]
[TD]B3
[/TD]
[TD]_
[/TD]
[TD]B4
[/TD]
[TD]_
[/TD]
[TD]B5
[/TD]
[TD]_
[/TD]
[TD]B6
[/TD]
[TD]_
[/TD]
[TD]B7
[/TD]
[/TR]
[TR]
[TD]EMP #
[/TD]
[TD]0050
[/TD]
[TD]0050
[/TD]
[TD]0050
[/TD]
[TD]0050
[/TD]
[TD]0050
[/TD]
[TD]0050
[/TD]
[TD]0050
[/TD]
[TD]0050
[/TD]
[TD]0050
[/TD]
[TD]0050
[/TD]
[TD]0050
[/TD]
[TD]0050
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PR 0-1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PR 2-5
[/TD]
[TD][/TD]
[TD][/TD]
[TD]2
[/TD]
[TD][/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD]3
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PR 6-9
[/TD]
[TD][/TD]
[TD][/TD]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
OK, I think I found out why none of my solutions are working but I don't know a fix for it. The cells I am searching all have =IFERROR(VALUE('01 Data'![cell reference]), ""). So my search array is full of value() functions. I thought maybe this might mess up my search so I tried a simple VLOOKUP and still got an error. I would try searching the source data but I'm not sure how I could do that. Here is what the source data looks like: (SEE ABOVE)

As you can see, the column labels (aisle) do not line up with the emp#. If I knew of a way to adjust the return value (aisle) to accommodate the shift in reference to the search value (emp#), then I could just use the source data.

(also, just a side note, all the numbers in my source data are stored as text.)

 

[pharside]

So I think the data source is my real problem. Anyone have an idea of how I can solve this?

 

[pharside]

ok, I've narrowed down my issue. I'm not sure why, but LEFT() function is not working with VLOOKUP(). Any idea why?

If I type in the VLOOKUP directly what I want to search, no problems. As soon as I use LEFT() to identify search criteria, I get an #N/A.

 

[pharside]

Postings like "=function" are not helpful; better post what value must obtain.

Based on your initial post, I'd venture:

=OFFSET(INDEX(B3:M3,MATCH(LEFT(Y4,4)&"*",B3:M3,0)),-1,0)

This solution works if I manually type in the MATCH criteria vs. using LEFT. Do you know why LEFT() isn't working?

 

[Aladin Akyurek]

This solution works if I manually type in the MATCH criteria vs. using LEFT. Do you know why LEFT() isn't working?

Care to post the value in Y4 as is and the value in B3:M3 with which it must match?

 

[pharside]

Care to post the value in Y4 as is and the value in B3:M3 with which it must match?

Y4 is 0050 - Mack, C. It is actually part of pivot table so it would change based on the source data when I refresh it. The only data that appears in B3:M3 are the emp#'s (i.e. 0050) that is why I am trying to use LEFT(Y4, 4) for my search criteria.

It may also be important to note that B3:M3 all have the following function:
=IF('01 Data'!B2=" MULT ", "MULT", IFERROR(VALUE('01 Data'!B2), ""))
This looks at my source data sheet to fill in the appropriate information.

 

[Aladin Akyurek]

Which cell of B3:M3 has this 0050?

 

[pharside]

Which cell of B3:M3 has this 0050?

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.

 

[Aladin Akyurek]

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.

Would you please just answer the question I posed? "Which cell of B3:M3 has this 0050 when we have LEFT(Y4,4) = 0050? Please do not feed more than just a straight answer to this question?