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
227 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
14 Comments
 
LVL 50

Expert Comment

by:barry houdini
ID: 38769000
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 33

Expert Comment

by:Rob Henson
ID: 38769010
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
ID: 38769101
Yes, they should all be either BO's or BX's, sorry about that.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 50

Expert Comment

by:barry houdini
ID: 38769141
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
ID: 38773254
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
ID: 38774779
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
ID: 38775747
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 38776591
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
ID: 38776695
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
ID: 38776701
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
ID: 38776772
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
ID: 38776811
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
ID: 38780912
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
ID: 38783720
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

751 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