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.

 

[Fluff]

Can you post an example of your data, there are some available here to help with that https://www.mrexcel.com/forum/about-board/508133-attachments.html

At the moment It's very clear to me what you are trying to do.

 

[pharside]

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[TD]O
[/TD]
[TD]P
[/TD]
[TD]Q
[/TD]
[TD]R
[/TD]
[TD]S
[/TD]
[TD]T
[/TD]
[TD]U
[/TD]
[TD]V
[/TD]
[TD]W
[/TD]
[TD]X
[/TD]
[TD]Y
[/TD]
[TD]Z
[/TD]
[TD]AA
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]A1
[/TD]
[TD]A2
[/TD]
[TD]A3
[/TD]
[TD]A4
[/TD]
[TD]B1
[/TD]
[TD]B2
[/TD]
[TD]B3
[/TD]
[TD]B4
[/TD]
[TD]C1
[/TD]
[TD]C2
[/TD]
[TD]C3
[/TD]
[TD]C4
[/TD]
[TD]D1
[/TD]
[TD]D2
[/TD]
[TD]D3
[/TD]
[TD]D4
[/TD]
[TD]E1
[/TD]
[TD]E2
[/TD]
[TD]E3
[/TD]
[TD]E4
[/TD]
[TD]F1
[/TD]
[TD]F2
[/TD]
[TD]F3
[/TD]
[TD]F4
[/TD]
[TD]0050 JOE
[/TD]
[TD][/TD]
[TD]=function
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]JOE
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]BOB
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4567
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1234 BOB
[/TD]
[TD][/TD]
[TD]=function
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]0050
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1234
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]JANE
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4567 JANE
[/TD]
[TD][/TD]
[TD]=function
[/TD]
[/TR]
</tbody>[/TABLE]

I cant paste anything, sorry. This is a simplified version but should give you an idea.

In AA I have tried to use a function that uses the list of people found in column Y to search for a match in row 3 and return the value found in row 1.

 

[pharside]

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[TD]O
[/TD]
[TD]P
[/TD]
[TD]Q
[/TD]
[TD]R
[/TD]
[TD]S
[/TD]
[TD]T
[/TD]
[TD]U
[/TD]
[TD]V
[/TD]
[TD]W
[/TD]
[TD]X
[/TD]
[TD]Y
[/TD]
[TD]Z
[/TD]
[TD]AA
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]A1
[/TD]
[TD]A2
[/TD]
[TD]A3
[/TD]
[TD]A4
[/TD]
[TD]B1
[/TD]
[TD]B2
[/TD]
[TD]B3
[/TD]
[TD]B4
[/TD]
[TD]C1
[/TD]
[TD]C2
[/TD]
[TD]C3
[/TD]
[TD]C4
[/TD]
[TD]D1
[/TD]
[TD]D2
[/TD]
[TD]D3
[/TD]
[TD]D4
[/TD]
[TD]E1
[/TD]
[TD]E2
[/TD]
[TD]E3
[/TD]
[TD]E4
[/TD]
[TD]F1
[/TD]
[TD]F2
[/TD]
[TD]F3
[/TD]
[TD]F4
[/TD]
[TD]0050 JOE
[/TD]
[TD][/TD]
[TD]=function
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]JOE
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]BOB
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4567
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1234 BOB
[/TD]
[TD][/TD]
[TD]=function
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]0050
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1234
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]JANE
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4567 JANE
[/TD]
[TD][/TD]
[TD]=function
[/TD]
[/TR]
</tbody>[/TABLE]

I cant paste anything, sorry. This is a simplified version but should give you an idea.

In AA I have tried to use a function that uses the list of people found in column Y to search for a match in row 3 and return the value found in row 1.

swap "Jane" and "4567." I accidentally put them in wrong but I can't edit.

 

[Aladin Akyurek]

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)

 

[pharside]

I tried the =offset solution you offered but I still get #N/A. The MATCH portion of the function is where the error begins as shown through the evaluation tool.

 

[Aladin Akyurek]

I tried the =offset solution you offered but I still get #N/A. The MATCH portion of the function is where the error begins as shown through the evaluation tool.

And is LEFT(Y4,4) in fact available in B3:M3? If so, care just to post that valur from B3:M3?

 

[pharside]

It is. The Left(Y4, 4) is referencing a pivot table but that shouldn't matter should it?

I was planning on using the solution that works as part of a series of IF's because the example above is only a small portion of my search area (I was trying to keep it simple). There is actually 6 more blocks of rows similar to the above example. I thought maybe I could use INDEX and MATCH but I'm not sure how. Essentially, I want to search an array and return the value of the cell 2 rows above the first instance found. Something like =INDEX(A1:M54, MATCH(LEFT(Y9, 4), A1:M54, FALSE)-2, MATCH(LEFT(Y9, 4), A1:M54, FALSE)) maybe?

https://flic.kr/p/PJtvHU Shows what I have. You will see that the list of names I am searching for is in column Y and my search array is A1:M54.

 

[Aladin Akyurek]

You must not try describing your problem by means of non-working Excel formulas. They can be misleading instead of guiding to a solution.


An image is also not helpful. Better to isolate the problem to a small sample along with the result which must obtain for that sample.

 

[pharside]

You must not try describing your problem by means of non-working Excel formulas. They can be misleading instead of guiding to a solution.


An image is also not helpful. Better to isolate the problem to a small sample along with the result which must obtain for that sample.

Go it.

OK, so in cells AA4:AA26 I want to show the cell contents (AISLE) 2 cells above the first match to the list in Y4:Y26. Essentially, return the contents 2 rows above a match.