In one column/field I've a value and I need to find this value in another sheet/column where this field contains a complete text including the value I'm looking for.
With the ctrl-h function I can find the specific value. But, I need a formula that takes the value and searches the whole column in the other sheet and returns a value in the same row so I can find the row where the valu can be found. Vlookup doesn't work for this.
Anyone a suggestion?
Thanks
In what column?
Either LOOKUP(SEARCH or INDEX(MATCH could do this, but you'd have to know which column you're returning the value from.
Where Sheet1!Z10 = what you're looking up
Sheet2 A1:A500 = place to look for it
Sheet2 B1:B500 = value to return
=LOOKUP(2,1/(SEARCH(Sheet1!Z10,Sheet2!A1:A500)),Sheet2!B1:500)
=INDEX(Sheet2!B1:B500,MATCH("*"&Sheet1!Z10&"*",Sheet2!A1:A500,0))
Make Mom proud: Add to my reputation if I helped out!
Make the Moderators happy: Mark the Thread as Solved if your question was answered!
Thanks for you reply. I do not fully understand your solution.
I give you more details.
I've sheet 1 with column 'D' containing a value (numbers). I need to find each value in column 'I' in sheet 2. But column 'I' contains in each field many characters and values. The value (column D) should be find in 'I' and then return a unique value in the same row 8 columns to the left.
ow do I fill the formula you are suggesting?
Thnaks for your help
So, the column I was asking for is... A on Sheet2? (8 columns to the left)
Let's say you start at D2:
Pick one.
=LOOKUP(2,1/(SEARCH(Sheet1!D2,Sheet2!$I$2:$I$500)),Sheet2!$A$2:$A$500)
or
=INDEX(Sheet2!$A$2:$A$500,MATCH("*"&Sheet1!D2&"*",Sheet2!$I$2:$I$500,0))
Then copy that formula from D2 all the way down.
I managed! I now understand your formula. Thanks a lot! I'm very happy with it. It saves me a huge effort. Thanks again.
how do I close this threat as being solved?
Glad I could help out.
At the top under Thread Options, click that dropdown menu and choose Mark as Solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks