Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

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

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
Dominator1025
Asked:
Dominator1025
  • 8
  • 5
1 Solution
 
barry houdiniCommented:
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
 
Rob HensonIT & Database AssistantCommented:
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
 
Dominator1025Author Commented:
Yes, they should all be either BO's or BX's, sorry about that.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
barry houdiniCommented:
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
 
Dominator1025Author Commented:
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
 
Dominator1025Author Commented:
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
 
Dominator1025Author Commented:
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
 
barry houdiniCommented:
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
 
Dominator1025Author Commented:
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
 
Dominator1025Author Commented:
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
 
barry houdiniCommented:
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
 
Dominator1025Author Commented:
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
 
barry houdiniCommented:
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
 
Dominator1025Author Commented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now