Solved

How can I find a string within a larger string and lookup that value in a different tab and give me a secondary value?

Posted on 2013-01-11
14
193 Views
Last Modified: 2013-01-16
Please see the attached file and help me write a formula for what I am looking for?

I need the formula to look within the set of objects in column A on sheet 1 and then find one of those objects in column A on sheet 2 and give me the value from column B on sheet 2 back onto column B of sheet 1.

Thanks!
Test--1.xlsx
0
Comment
Question by:Dominator1025
  • 8
  • 5
14 Comments
 
LVL 50

Expert Comment

by:barry houdini
Comment Utility
In your example there don't seem to be any matches because all the search items begin "BX" and all the others are "BO"?

Assuming 1 or 0 matches per row this formula should work for B2 copied down

=IFERROR(LOOKUP(2^15,SEARCH(Sheet2!$A$2:$A$6,Sheet1!A2),Sheet2!$B$2:$B$6),0)

.....but as I say that will give you all zeroes for the data "as is". If I change all the "BO"s to "BX"s I get the results you wanted - see attached

regards, barry
Test--1-barry.xlsx
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
See attached.

Assumes consistency in column A of sheet1 based on first entry within string.
Added helper column on sheet2. Have added to left of data and used vlookup, can add further to right and use different function if so required.

Thanks
Rob H
Test--1.xlsx
0
 

Author Comment

by:Dominator1025
Comment Utility
Yes, they should all be either BO's or BX's, sorry about that.
0
 
LVL 50

Expert Comment

by:barry houdini
Comment Utility
OK, I think my suggested formula will work for you then......or this version will sum multiple criteria for any row

=SUMPRODUCT(ISNUMBER(SEARCH(Sheet2!$A$2:$A$6,Sheet1!A2))+0,Sheet2!$B$2:$B$6)

regards, barry
0
 

Author Comment

by:Dominator1025
Comment Utility
Barry,

Can you please explain the parts of this formula? I have been trying to implement it in various other fields but I keep getting a zero value.  I mostly don't understand the 2^15 in the Lookup function, but maybe a quick tutorial would help most.

=IFERROR(LOOKUP(2^15,SEARCH(Sheet2!$A$2:$A$6,Sheet1!A2),Sheet2!$B$2:$B$6),0)

The Sumproduct formula worked great for something in the same file I actually needed to use it for!

Thanks!
0
 

Author Comment

by:Dominator1025
Comment Utility
I now understand the 2^15, but am experiencing a little trouble with implementing the formula on a large scale for some reason.  Do both fields in the search function need to be the same format? Such as general, text, or custom?  I will fiddle a little more today and report back as soon as I can if I need more help, thanks!
0
 

Author Comment

by:Dominator1025
Comment Utility
Barry,

I updated my file with more data and keep getting zeros unless I change the order of the fields in the search function. As you gave it to me, I get zeros. If I switch them, it appears that I get a result that acts more like a VLookup, but both fields must be equal.

This might be a better example of what I am looking for. I have looked up some of the answers the formula should be giving me, however I am only getting zeros.

Did I screw something up?

Thanks,
Dom
Test--1-barry.xlsx
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 50

Expert Comment

by:barry houdini
Comment Utility
OK Dom, there are a few different issues:

1) I don't think you were referencing the correct columns.

2) Some of the early values in the "Object" range (in Percentages sheet) are "A"s (or "B"s) so this matches with any "A" in the multi object range - I needed to change the formula so it only matches with the whole part between the commas.

3) Some of the "Object" range values in Percentages sheet have trailing spaces so I used SUBSTITUTE function to remove those.

The resulting formula is as follows:

=IFERROR(LOOKUP(2^15,SEARCH(SUBSTITUTE(","&Percentages!$A$2:$A$15000&","," ",""),","&A2&","),Percentages!$B$2:$B$15000),"Missing Data")

see attached

regards, barry
Test--1-barry2.xlsx
0
 

Author Comment

by:Dominator1025
Comment Utility
Thanks Barry, I think we are almost there.

On the attached file, I went to the percentage tab and deleted the letters off the end of the strings that begin with BO01 and BO02. Those now come back as "Missing Data".

How can we get those cells to still show the correct answers in C2 and C3?  

I know I keep throwing curveballs at you, but I'm still trying to get it down as simply as my question, to find a string within a larger string and lookup that value in a different tab and give me that value back at the larger string.

I really appreciate your time on this!

Thanks,
Dom
Test--1-barry2.xlsx
0
 

Author Comment

by:Dominator1025
Comment Utility
To clarify, I would like to get it to an all encompassing formula that can account for the strings being exactly the same, the strings having 1 of many values between commas, or more than one of the values between commas, or even just 4 out of the 5 characters that are in any of the values between the commas in column A on the Formula tab.
0
 
LVL 50

Expert Comment

by:barry houdini
Comment Utility
OK Dom, I suspect that will be a little tricky - way past my bedtime here so I'll have to get back to it tomorrow

regards, barry
0
 

Author Comment

by:Dominator1025
Comment Utility
Thanks Barry, if it needs to be a different formula, I could probably live with that. I have about 15 tabs with various fields containing those objects.  Some have the letter at the end, some don't, but they should be consistent each time the tabs get refreshed.  Thanks again.
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
Comment Utility
OK, sorry for the delay, Dom

I virtually had to repeat the formula to check for the first 4 characters if there isn't a full match so that now becomes this:

=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)),"Missing Data")

See revised attachment

You also mentioned checking for multiple matches - I suspect that's a step too far for a formula approach unless you start splitting out the comma separated values into individual cells

regards, barry
Test--1-barry3.xlsx
0
 

Author Closing Comment

by:Dominator1025
Comment Utility
Perfect Barry, thank you so much for your time!

I also need 1 more set of variables handled, but it is different so I will submit a new question. Please look for it if you get a chance.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now