index, match, offset, vlookup, formula help pls

[orsm6]

Hi all, trying various formulas and not sure if i am on the right track

one formula that almost works is vlookup, but i need the look up to return the value that is 3 rows below. so not sure if i now have to use OFFSET or INDEX etc. the other tricky bit is that the formula needs to lookup the cell containing a number, find it in a cell containing number / text and then return the value 3 rows down....lol

=VLOOKUP(E5&"*",'[PSR GCPB AU85 2018-12-12.xlsm]Paste here'!$A$1:$G$500,7,FALSE)

this returns 21, but the number in the 3rd row below is 11428

tia for any help.

 

[jtakw]

Hi,

Can you adapt this offset LOOKUP to your data (notice the Result vector is 3 rows offset from the Lookup vector):

Book1
	A	G	H	I
1	1bbb	111	2	555
2	2ccc	222
3	3ddd	333
4		444
5		555
6		666
Sheet386

Cell Formulas
Range	Formula
I1	=IFERROR(LOOKUP(2,1/SEARCH(H1,A1:A3),G4:G6),"No match")

 

[orsm6]

hi thanks for you assistance.

on the lookup sheet there are 300 rows or so.
in column A there is number/text
there are 6 lines that have the same number/text, then the next 6 have different number/text and so on.

this formula for me is returning the 6th row, i can't get it to return the value in the 3rd.

=LOOKUP(E5&"*",'[PSR GCPB AU85 2018-12-12.xlsm]Paste here'!$A$2:$A$373,'[PSR GCPB AU85 2018-12-12.xlsm]Paste here'!$G$2:$G$373)

if i increase the result vector by 3 rows it only pushed the desired result further down

 

[orsm6]

i see what i did wrong, i now have got this working... thanks again.

Hi,

Can you adapt this offset LOOKUP to your data (notice the Result vector is 3 rows offset from the Lookup vector):

	A	G	H	I
1	1bbb	111	2	555
2	2ccc	222
3	3ddd	333
4		444
5		555
6		666

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet386​

Worksheet Formulas Cell Formula I1 =IFERROR(LOOKUP(2,1/SEARCH(H1,A1:A3),G4:G6),"No match") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[jtakw]

You're welcome, glad you got it working.