vlookup and return from same row

[velohead]

Hi All,


I just want to do a vlookup (albeit into one row) and return a value 3 cells to the right of the found value.



The long version….

Sheet ‘Report’ contains some dates, and I wish to vlookup (or equivalent) into sheet ‘Data’ row 1, find the date then return the value 3 cells to the right of the found date.

Sheet ‘Data’ row 1 is very wide and contains lots of dates, but the value I want to return is always 3 cells right of the date.

Hope this is clear.



I only really use vlookups, sumif, and sumifs.

I am not up-to-speed on offset, index, match etc which may give the answer.



Any ideas at all ?

 

[SunnyAlv]

Can you include a picture or table of what the data looks like to make it easier for someone to know the achievements you want further?
and ur Excel version ?

 

[AlanY]

try this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	05/02/2024	4	1	2	3	4	5	6	05/02/2024	7	8	9	29/01/2024
2	29/01/2024	7
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=OFFSET($A$1,0,MATCH(A1,$C$1:$M$1,0)-2,1,1)

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

See if this is the sort of thing you want.

velohead.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		1/02/2024	a	b	c	2/02/2024	d	e	f	3/03/2024	g	h	i
Data

velohead.xlsm
	A	B
1	Date	3 right
2	2/02/2024	f
3	1/02/2024	c
Report

Cell Formulas
Range	Formula
B2:B3	B2	=INDEX(Data!$1:$1,MATCH(A2,Data!$1:$1,0)+3)

 

[AlanY]

try this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	05/02/2024	4	1	2	3	4	5	6	05/02/2024	7	8	9	29/01/2024
2	29/01/2024	7
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=OFFSET($A$1,0,MATCH(A1,$C$1:$M$1,0)-2,1,1)

my bad, mis-read the question to 3 to the left.

 

[velohead]

Thank You Peter_SSs, the solution works perfectly. Like a dream in fact. This site is such a great resource.

Sorry for not stating excel version, I will do do in future.

 

[Peter_SSs]

Thank You Peter_SSs, the solution works perfectly. Like a dream in fact. This site is such a great resource.

You're welcome. Thanks for the follow-up.

Sorry for not stating excel version, I will do do in future.

Instead of stating each time, why not include it in your account details (ie profile) as suggested above so that the information is always readily available to your helpers & you don't have to remember to state it for each new thread. Like this ..