=IFERROR(VLOOKUP(MATCH(TRUE,COUNTIF(INDIRECT(ShName),A2)>0,0),$H$2:$I$4,2,0),"Not Found")
Starting from the centre of the formula.
ShName is a definded name for the contents of the range I2:I4
INDIRECT makes the defined name into an actual useable reference to another worksheet
COUNTIF counts the occurrences of the value in A2 in the list of worksheets and their cell ranges and is looking for a value greater than 0
The COUNTIF and INDIRECT are inside the MATCH function. MATCH takes 3 arguments. First the value which is A2, second is the array which in this case is defined by the INDIRECT function and lastly the 0 means an EXACT match must be found.
The MATCH will return a value in this case from 1 to 3 if the value in A2 is found or an error if it is not found.
VLOOKUP will lookup the value that is found in the range $H$2:$i$4 and return the value in the second column (the 2) that matches the value that MATCH found. The 0 means an EXACT match must be found in the first column.
IFERROR if the VLOOKUP results in an error, the value at the end of the formula "Not Found" will be returned.

All of this is based on the formula that Sadath31 first gave to you. It is just expanded a bit.