Possible IF THEN statement?

[C_merr]

What is the best formula to use if I want to (in sheet 1) search for the cell that contains the text "WHEAT-SRW" but not an exact match, and return the value in column H that is in the same row as "WHEAT-SRW"

 

[sheetspread]

Excel 2010
	A	B	C	D	E	F
2	q	2
3	w	6
4	e	9
5	543fv gg WHEAT-SRW	11
6
7					WHEAT-SRW	11
Sheet4

Cell Formulas
Range	Formula
F7	=VLOOKUP("*"&E7&"*",$A$2:$B$5,2,0)

If the search term appears just once (adjust the range and column number for H)

 

[C_merr]

Instead of referencing WHEAT-SRW from a different cell can I replace E7 with WHEAT-SRW? because the only place that it is found on a the sheets is in the first sheet in column A wherever it appears (For example, in this case it would only occur at A5. The data in Sheet 1 changes each week, and has many columns of different values, i then pull some of those values and put them in a new row each week on sheet two. The reason this will be helpful and I can't link a cell is because each week i have found that the commodities may switch orders in sheet one.

 

[sheetspread]

E7 already contains WHEAT-SRW, do you mean put the adjacent value (11) in that cell instead of having them separate?

 

[sheetspread]

You can put that E7 value anywhere by the way, you're not linking a fixed cell. The lookup is flexible.

 

[CharlesChuckieCharles]

VLOOKUP("*"&E7&"*",$A$2:$B$5,2,0)

Do you mean

VLOOKUP("*"&"WHEAT-SRW"&"*",$A$2:$H$5,2,0)

OR VLOOKUP("*"&AA1&"*",$A$2:$H$5,2,0) or VLOOKUP("*"&Z1&"*",$A$2:$H$5,2,0)


Either way will work

But be aware

if the value is "WHEAT SRW" or "WHEA-SWR" etc.. the "*" 's won't help

 

[bhos123]

instead of referencing wheat-srw from a different cell can i replace e7 with wheat-srw? Because the only place that it is found on a the sheets is in the first sheet in column a wherever it appears (for example, in this case it would only occur at a5. The data in sheet 1 changes each week, and has many columns of different values, i then pull some of those values and put them in a new row each week on sheet two. The reason this will be helpful and i can't link a cell is because each week i have found that the commodities may switch orders in sheet one.

=vlookup("*"&"wheat-srw"&"*",$a$2:$b$5,2,0)

 

[C_merr]

Perfect, I got it! Thank you for all of the help!