Search defined row for a string, return value 2 columns to the right of located string-CodePudding

I'm using google sheets, and trying to look up the string value in cell A2, check row 2 of Sheet2 for matching string, then (once found) return the value in the cell 2 columns to the right of the found string.

=INDEX(Sheet2!$A$2:$2,MATCH($A2,Sheet2!$A$2:$2,2))

I thought this would work, but it unfortunately does not. Nor do my other attempts.

I've tried a variety of googled suggestions, but most seem to show a vertical column being searched rather than a horizontal row, and/or return a value in a known/fixed column, rather than a relative one.

Sheet here with example, feel free to edit; https://docs.google.com/spreadsheets/d/1ZtgrCepZivt7AHH6ShC3SQOXWKUqmzl5qickPE9O93c/edit?usp=sharing

Any help here would be much appreciated!

CodePudding user response：

What about this?. Puting a 1 to indicate that's in the same row and adding 2 to the value of MATCH:

=INDEX(Sheet2!$A$2:$2,1,MATCH($A2,Sheet2!$A$2:$2,0) 2)