I need to find a string within a string and return a value from a different spreadsheet.

Do you want to try using VBA?

Public Function LookupCode(code As String) As String
Dim result() As String
Dim rng As Range
Dim c As Variant

result = Split(code, ",")

For Each c In result
    Set rng = shPercentages.Range("A2:A15000").Find(c, LookIn:=xlValues)
    If Not rng Is Nothing Then
        LookupCode = FormatPercent(rng.Offset(ColumnOffset:=1).Value)
        Exit Function
    End If
Next

LookupCode = "Missing Data"

End Function

Select allOpen in new window

Test--1-barry3.1.xlsm

Try this formula in B2

=IFERROR(IFERROR(LOOKUP(2^15,SEARCH(SUBSTITUTE(","&Percentages!$A$2:$A$15000&","," ",""),","&A2&","),Percentages!$B$2:$B$15000),LOOKUP(2^15,SEARCH(SUBSTITUTE(","&LEFT(Percentages!$A$2:$A$15000&"$$$$",4)," ",""),","&A2),Percentages!$B$2:$B$15000)),INDIRECT(ADDRESS(ROW()-1,COLUMN())))

JPaulino,   If VBA would account for any combination of the Objects in either tab, I would be very open to learning more about it. I also imagine it would not be as big of a memory hog as the large formulas, correct?

That said, how can I get the VBA code to work for all cells in column B?  When I copy the =LookupCode(B22) to the other cells everything turns into this " #NAME? "

Did you try the formula I proposed?

ssaqibh, With the formula you provided, it does give the same value for B21 and B22, but it also is providing false data in other locations.  Please check out the new file to see what I mean. It looks like your file is simply returning the valu from the previously found cell.

Thanks!
Test--1-barry3.2.xlsm

Dominator1025

That is exactly what I had understood and attempted.

Now with the given example how would one know which of the objects could use the previous value and which of them should look for their own?

ssaqibh, I'm sorry I wasn't more clear. I had already gone through this in much greater detail with Barry Houdini in a previously submitted question and came across a new wrinkle that I needed to iron out.

In that last file I added, I want the formula in B21 of the Formulas tab to find a string of text in Column A of the Percentages tab that matches, at least the first 4 characters of one of the sets of 4 or 5 character strings in the text in A21 of the Formulas tab, and then return the value in column B of the Percentages tab. Does that make sense?

The first file I included with my initial question above, handles this perfectly, except for the case of A542 on the percentages tab. I was hoping that would be an easy adjustment.

Thanks for your help!

That said, how can I get the VBA code to work for all cells in column B?  When I copy the =LookupCode(B22) to the other cells everything turns into this " #NAME? "

Have you activated the macros? It works if you copy the formula to other rows.

JPaulino,

Ok, got the Macros fired up. It is having a problem with the objects that are missing the 5th character in the string, which is a possibility I need included.

I will eventually teach myself VBA, but could you please explain the code that you have given me? This seems like a great solution, but I will need to expand it to other worksheets and ranges and would like to make sure I understand what I am doing.

Thanks!
Test--1-barry3.1VBA.xlsm

Ok, got the Macros fired up. It is having a problem with the objects that are missing the 5th character in the string, which is a possibility I need included.

Can you explain this? Maybe with an example

JPaulino,
Please check out the file I attached in my previous post, labeled VBA 2.0.

Check the lastest version with some commets in code
Test--1-barry3.1VBA.xlsm

JPaulino,

Thanks, this looks like it gets everything I need.

If I wanted to change the lookup to a different sheet with a bigger range how would I change the code?  Lets say from the last file, the "Formulas" tab stays the same, but I want to look at a different tab called "New Sheet" and the object I am looking for is still in column A on "New Sheet", but the values on "New Sheet" that I want returned are in column AH.

Can you please show me how the code would look with those new parameters:
"New Sheet" and column AH.

Thanks again, I appreciate it!

You can change the sheet name like this:

Set rng = Sheets("Percentages").Range("A2:A15000") ....

Just change Percentages with the correct name and in the offset value (ColumnOffset:=1) to the offset columns you need,

Jpaulino,

I am having trouble editing the code to rollout to other files. I am still getting the  #NAME? error again and I am sure I have macros enabled. I think I might be missing something.  

Is 'LookupCode' the name you gave or a specific VBA function command? I was hoping to name each function something different as I have to create various lookups in a workbook. So could this new one be 'LookupNewSheet'?

Can you please add code to the file attached that will pull the values from the 'New Sheet' from column AH?

Thanks!
Test--1-barry3.2VBA.xlsm

Jpaulino,

I can't seem to get the code running on my big file. Can you please walk me through how to implement the code in a macro-enabled workbook that has no code when you open Visual Basic?

I am fairly new to VBA, but I pick things up quickly and just need a little extra help to get going here, thanks!

I think I got it, I didn't have the code in a module, it was in a worksheet. This should close out the question, thank you so much!!!

Can you please recommend some learning material for someone that is already pretty handy with Excel and Access but wants to take things to the next level with VBA?

You have some nice articles here in EE and you can also look in youtube for VBA tutorials ... you have a lot there.

Glad I could help!