Lookup, answer offset

[Taff]

Looking for some help with a formula:

Looking to find a value in sheet 1, dependent on a value in column A in sheet 2.

seems fairly simple but, the the value required is not on a direct linear view.

so

Column A, row 4 in sheet 2 = "fred"
Somewhere in column B on sheet 1 = "fred"
The response back I need is then in sheet 1, column Q, but 2 rows below. eg "fred" is in B26, response required is in q28

I'm ok with vlookups, xlookups etc, but its this offset that i cant get.

any help appreciated

 

[Fluff]

How about

=XLOOKUP(A2,Sheet1!$B$2:$B$1000,Sheet1!$Q$4:$Q$1002,"no match")

 

[Taff]

Unsure of the actual mechanics of how that worked, but looks like it worked a treat.

Many Thanks

Appreciated

 

[Fluff]

You're welcome & thanks for the feedback.