Search for string of numbers within a range of columns and rows

[meppwc]

I am struggling with creation of a formula that will perform the following:

On worksheet "Summary", search for the value found in B4 using worksheet "Table" range A2:F24, and if found on a given row, return the value in the 6th column of that row.

 

[mikerickson]

Is there a specific column of A2:F24 where the value would be found.
If so, a VLOOKUP type formula would be the way to go.

 

[steve the fish]

Maybe this:

=INDEX($F$2:$F$24,AGGREGATE(14,6,(($A$2:$E$24=Summary!$B$4)*ROW($A$2:$E$24))-ROW($A$2)+1,1))

 

[DanteAmor]

If the value can be in any column, then try the following formula array


{=IFERROR(INDEX(Table!F2:F24,MAX(IF(-ISNUMBER(SEARCH(B4,Table!A2:F24)),ROW(F2:F24)))-1), "Not exist")}

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself.