Lookup value in table, return the cell above

[yits05]

Hello,

This is probably simple but I can't seem to figure this out. I would like a formula that looks up the value in A1 to find the match in a table, say d5:J20, and return the cell directly above the matched cell.

Any advice would be appreciated.

 

[Alex Blakenburg]

If you use Index Match, you find the Row using Match then just subtract the number of rows you need to get the row you actually want.
In the case of 1 row before, you might want to include the header row in your range in case what you find is in the first data row.

In the below I have used cell for to get the row, but you could either hard code that or use a match to work out the Column as well.

20210323 Index Match offset 1 row.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	30
2
3	Return Value
4	Column No			Col1	Col2	Col3	Col4	Col5	Col6	Col7
5	2	220		10	210	310	410	510	610	710
6	3	320		20	220	320	420	520	620	720
7				30	230	330	430	530	630	730
8				40	240	340	440	540	640	740
9				50	250	350	450	550	650	750
10				60	260	360	460	560	660	760
11
Workings

Cell Formulas
Range	Formula
B5:B6	B5	=INDEX($D$4:$J$10, MATCH($A$1,$D$4:$D$10,0)-1,$A5)

 

[yits05]

If you use Index Match, you find the Row using Match then just subtract the number of rows you need to get the row you actually want.
In the case of 1 row before, you might want to include the header row in your range in case what you find is in the first data row.

In the below I have used cell for to get the row, but you could either hard code that or use a match to work out the Column as well.

View attachment 35044

20210323 Index Match offset 1 row.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	30
2
3	Return Value
4	Column No			Col1	Col2	Col3	Col4	Col5	Col6	Col7
5	2	220		10	210	310	410	510	610	710
6	3	320		20	220	320	420	520	620	720
7				30	230	330	430	530	630	730
8				40	240	340	440	540	640	740
9				50	250	350	450	550	650	750
10				60	260	360	460	560	660	760
11
Workings

Cell Formulas
Range	Formula
B5:B6	B5	=INDEX($D$4:$J$10, MATCH($A$1,$D$4:$D$10,0)-1,$A5)

Thank you, Alex.

Just to clarify, however, the lookup value may be in any column in the table. So, for example, as below I would want like a formula that would populate in B2:B4 that search for the values in A2:A4, and returns the cell above. Thanks again

Value to Search	Result:		Column1	Column2	Column3
abc	ipsum		hello	ipsum	filler
def	world		world	abc	text
ghi	here		def	lorem	here
			random	word	ghi

 

[Alex Blakenburg]

Are you only looking at a very small number of columns ? If so it would be possible using that concept with IFS.
If its a large number of columns or needs to be scalable, I will have a look but we may need to look to someone else to come up with some suggestions.

Is a helper column acceptable ?

 

[yits05]

Are you only looking at a very small number of columns ? If so it would be possible using that concept with IFS.
If its a large number of columns or needs to be scalable, I will have a look but we may need to look to someone else to come up with some suggestions.

Is a helper column acceptable ?

Hi, yes, helper column is totally fine. There wont be more than ~6 columns.

 

[Peter_SSs]

Here is one way (assuming values can only occur once in Column1:Column3)

21 03 23.xlsm
	A	B	C	D	E	F
1	Value to Search	Result:		Column1	Column2	Column3
2	abc	ipsum		hello	ipsum	filler
3	def	world		world	abc	text
4	ghi	here		def	lorem	here
5				random	word	ghi
Cell above

Cell Formulas
Range	Formula
B2:B4	B2	=INDEX(D:F,AGGREGATE(15,6,ROW(D$2:F$5)/(D$2:F$5=A2),1)-1,AGGREGATE(15,6,(COLUMN(D$2:F$5)-COLUMN(D2)+1)/(D$2:F$5=A2),1))