Returning value above searched row

pharside

New Member
Joined
Oct 7, 2018
Messages
18
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.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
AA
1
A1
A2
A3
A4
B1
B2
B3
B4
C1
C2
C3
C4
D1
D2
D3
D4
E1
E2
E3
E4
F1
F2
F3
F4
0050 JOE
=function
2
JOE
BOB
4567
1234 BOB
=function
3
0050
1234
JANE
4567 JANE
=function

<tbody>
</tbody>

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.
 
Upvote 0
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
AA
1
A1
A2
A3
A4
B1
B2
B3
B4
C1
C2
C3
C4
D1
D2
D3
D4
E1
E2
E3
E4
F1
F2
F3
F4
0050 JOE
=function
2
JOE
BOB
4567
1234 BOB
=function
3
0050
1234
JANE
4567 JANE
=function

<tbody>
</tbody>

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.
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top