Wich function should I use here?

[eurano]

Hello,
Looking for help on this. I want cell E2 in sheet 1 to show certain "mth" to match "service date" and "serial number" from sheet 2. In result green cells in both sheets should show the same value.
Img:
Pasteboard — Uploaded Image

 

[Oeldere]

=VLookup($a2,sheet2!$a$4:$K$100,11,0)

 

[AlanY]

try this in E2 and copy down, adjust ranges to suit

=INDEX(Sheet2!$D$1:$Z$20,MATCH($B2,Sheet2!$B$1:$B$20,0),MATCH($D2,OFFSET(Sheet2!$D$1,MATCH($B2,Sheet2!$B$1:$B$20,0)-1,0,1,20),0)+1)

 

[eurano]

try this in E2 and copy down, adjust ranges to suit

=INDEX(Sheet2!$D$1:$Z$20,MATCH($B2,Sheet2!$B$1:$B$20,0),MATCH($D2,OFFSET(Sheet2!$D$1,MATCH($B2,Sheet2!$B$1:$B$20,0)-1,0,1,20),0)+1)

Worked like a charm. THANKS!

 

[AlanY]

you're welcome