finding a string in an array, and getting the value next to it?

[deLockloire]

hi,

i'd like to find a string in a table (e.g., A1:H200). the string can be anywhere within this table. once the string is found, i'd like to get the data from the next cell to the right (e.g., if the string is found in F32, then i'd like the formula to show the content of G32).

if necessary, i can arrange the workbook in a way, that the string to be found will be either in column A or in column F. this narrows down the number of columns where i can search for the string.

any ideas?

 

[mikerickson]

If you were working with one column, VLOOKUP would be the way to go.

In the A, F scenario, if there is only one cell with theString and the cell next to it is text, a formula like this would work.
=IF(ISNUMBER(MATCH("theString,A1:A200,0),VLOOKUP("theString",A1:A200,2,False),"") & IF(ISNUMBER(MATCH("theString,F1:F200,0),VLOOKUP("theString",F1:F200,2,False),"")

If the cell next to theString is numeric, change "" to 0 and & to + in the formula.

 

[deLockloire]

good idea. much better than my original, because this way i can use the same string in the A and the F columns.

thanks.