Search value from table based on cell value

[jb]

I have an excel file with 2 sheets - s1 and s2.
In s1 shhet I am entering a value in a1 cell for e.g. HM

I have a table on s1 sheet from a3 to i6.
a3 contains value 1 i.e. slot no. 1 and a5 contains value 2 means slot no. 2.

I want values FA and 12 in cells B3 and B4 respectively.
I want values TB and 16 in cells B5 and B6 respectively.

For slot 1 - HM value will be searched from table on s2 sheet - a1 to L7.
HM is found in B2. In B9 and B10 values are FA and 12.

For slot 2 - HM value will be searched from table on s2 sheet - a13 to L19.
HM is found in G15. In G21 and G22 values are TB and 16.

I am attaching test sheet. I need formula.
Thanks in advance.

 

[Luke M]

See attached. Formula uses INDEX and SUMPRODUCT.

 

[jb]

Thanks. It worked.

 

[jb]

Well a small problem arised Luke M.
If I type YP in a1 column of s1 sheet, then it gives me wrong output.

For slot 1 - YP value will be searched from table on s2 sheet - a1 to L7.
YP is not found. So it must display blank in b3 and b4.

For slot 2 - YP value will be searched from table on s2 sheet - a13 to L19.
YP is found in L14. In L21 and L22 values are SB and 20. So it must display SB and 20 in b5 and b6 respectively.

Please help.

 

[Luke M]

Updated with an error trap for when value isn't found.

 

[jb]

Thanks a lot Luke M.
Working perfectly.
Thanks again.