if text exists in cell, then match a 2nd value to sheet2, and return 3rd value from sheet2

[SBLee]

need formula that will index/match based on if specific text exists in cell
text can be upper or lower case

if text "abcd*fr*" is not in cell (sheet1 H2) then return empty cell
if text "abcd*fr*" is in cell (sheet1 H2) then
match sheet1 G2 to sheet2 E2:E17
return value from sheet2 D

 

[FDibbins]

maybe this?

=if(sheet1!H2="abcd*fr*",index(sheet2!$d$2:$d$17,match(G2,sheet2!$e$2:$e$17,0)),"")

 

[SBLee]

looking for a formula that based on specific text being present in col H, will index/match to sheet2, and return value in col D

 

[SBLee]

going to go try it thanks, have so many tries at this -- but nothing with ,''") ---- have to go look that up

 

[FDibbins]

"" returns a "nothing" or a "blank" cell.
If A1 contains 1...
=if(A1="","cell empty","cell not empty") this will return "Cell not empty"

If A1 is empty...
=if(A1="","cell empty","cell not empty") this will return "Cell empty"

 

[SBLee]

the double quotes gives me the 'empty cell' ; but I think maybe I'm not identifing the text properly, getting no returned value when the text matches. think I need some type of Trim. the text can be in a number of formats ABCD - FR or abcd-front ---- any suggestions????

 

[FDibbins]

If you know that what you are looking, does exist in your data, it may have leading/trailing spaces - test with =exact(fre1,ref2) FALSE indicates that they are not the same

 

[SBLee]

the problem is definately the way I am creating the text string in the formula. I thought the asterik would allow for the 'space, hyphen, space' and any additional letters after the fr. but it isn't working unless it is an exact copy of what is in the cell. Here is an example:

ABCD - front/top
xyzzzzz
abcd-fr
xyyyyyyy
eeeeeee
abcd- front

I need the text string to work with any version of abcd fr
"abcd*fr*" does not work, think maybe I need " around the asterick, will have to do some trial & error versions to figure it out.

 

[FDibbins]

Your search method should work just fine....
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

1​

[/td][td]ABCD - front/top[/td][td]

1​

[/td][/tr]

[tr][td]

2​

[/td][td]xyzzzzz[/td][td]

#VALUE!​

[/td][/tr]

[tr][td]

3​

[/td][td]abcd-fr[/td][td]

1​

[/td][/tr]

[tr][td]

4​

[/td][td]xyyyyyyy[/td][td]

#VALUE!​

[/td][/tr]

[tr][td]

5​

[/td][td]eeeeeee[/td][td]

#VALUE!​

[/td][/tr]

[tr][td]

6​

[/td][td]abcd- front[/td][td]

1​

[/td][/tr]
[/table]
B1 down =SEARCH("abcd*fr*",A1,1)