Some sort of lookup function?

[damian_r_Home]

Morning All.
I think this is some sort of lookup function but it needs to look up for some of the text within a cell rather than the entire contents.....for example

As you can see, cell C6 contains a manually entered string of text.
In cell I6 I need it to look at Sheet1 (in the same workbook), cell range B$6:B$100 and look for the text "Terracotta - helping hands". Assuming that for this example it finds that text in cell B54, I would want cell I6 to show the contents of Sheet1 cell C54

As mentioned before, its some kid of LOOKUP but the fact its only looking up part of the cell contents is what I've no idea how to do.

Hope this makes some sort of sense

Stay safe

D

 

[jorismoerings]

Hi,

Searching columns with a partial string from a cell is possible however the difficulty lies within the way the data is entered in your worksheet.
So:

1. Is the way data entered always structured to the example given or is at random by choice of user (eg could the text also be E-Helping hands - BH - Terracotta?)
2. What the way the range is filled in Sheet 1 - does terracotta only mentioned once in this table?

I suggest you provide some more info on both tables. Use XL2BB to make it easy to copy and work with your data.
More info on XL2BB to be found here

 

[damian_r_Home]

Morning Jorismoerings

Thanks for posting on this and in answer to your questions.

1) Yes, the format will always be the same. The only difference will be that sometimes the BH-E will be another combination of letters
2) No, Terracotta will appear numerous times, such as "Terracotta - Tiles", "Terracotta - Rails", "Terracotta - Fixings" and likely other instances as well. THere will also be other instances where there will be "Aluminium - Rails", "Aluminium - Fixings" etc

Hope this helps

 

[jorismoerings]

Hi,

Yes, the format will always be the same. The only difference will be that sometimes the BH-E will be another combination of letters

What's the variety of letters used in the BH-E part hence can the partial string in the cell be found after the second "-" in the string?

Assuming that's true, you could try something like this:

Book1
	C	D
5
6	BH-E-Terracotta - Helping hands	10
7
Sheet1

Cell Formulas
Range	Formula
D6	D6	=VLOOKUP(RIGHT(C6,LEN(C6)-FIND("~",SUBSTITUTE(C6,"-","~",2))),Sheet2!$B$6:$C$100,2,0)

 

[damian_r_Home]

Thankyou.

Just tried it and for some reason I cant figure out, it isn't getting the result from the neighbouring column on sheet2

If I tweak the formula as highlighted in bold below, it does actually return the value in col B of Sheet2 - but if I change it to say 3, it does not return the value in col D on Sheet2
=VLOOKUP(RIGHT(C6,LEN(C6)-FIND("~",SUBSTITUTE(C6,"-","~",2))),Sheet2!$B$6:$C$100,1,0)

one last cheeky thing, is there any chance that the rather than the start of the data entry being "BH-E-Terracotta - Helping Hands" can we get a way for the formulas to work if we have "BH-E - Terracotta - Helping Hands" ...


just a side thought, but would the formula work more easily if we where to type ~ instead of - as part of our manual data entry?

 

[jorismoerings]

Hi,

but if I change it to say 3, it does not return the value in col D on Sheet2

If you want the formula to return the D column you need to change the formula to this =VLOOKUP(RIGHT(C6,LEN(C6)-FIND("~",SUBSTITUTE(C6,"-","~",2))),Sheet2!$B$6:$D$100,3,0)

Yep the formula would be shorter but not particular more easy or faster. The virtual replacement of the 2nd - in the string with a ~ is just a helper for the used search function. If you could get rid of it, fine but also think about data entering, Typing a ~ isn't the most easiest while the - is a straight character on the keyboard. So basic question would be: why would you even want it to?

 

[damian_r_Home]

Yessss, that is fantastic...it works a treat!!!!!!

I'll stick with - rather than ~ as suggested

any thoughts on my cheeky thing? (ooooer Misses!!) in that is there any chance that the rather than the start of the data entry being
"BH-E-Terracotta - Helping Hands" can we get a way for the formulas to work if we have
"BH-E - Terracotta - Helping Hands" which has a space before and after the 2nd -

 

[jorismoerings]

Yessss, that is fantastic...it works a treat!!!!!!

I'll stick with - rather than ~ as suggested

any thoughts on my cheeky thing? (ooooer Misses!!) in that is there any chance that the rather than the start of the data entry being
"BH-E-Terracotta - Helping Hands" can we get a way for the formulas to work if we have
"BH-E - Terracotta - Helping Hands" which has a space before and after the 2nd -

=VLOOKUP(TRIM(RIGHT(C6,LEN(C6)-FIND("~",SUBSTITUTE(C6,"-","~",2)))),Sheet2!$B$6:$D$100,3,0)

be aware: this will only work if they don't make any Typo's in the last part of Terracotta - Helping hands. This part needs an exact match to the text in column B on Sheet 2

 

[damian_r_Home]

Absolutely fantastic!!!!

In many ways, the fact they MUST match the text exactly is a good additional thing!!!!!

Many many thanks!

 

[jorismoerings]

Glad i could help.

Just to know: even the typo things can be solved but depends on the way data is entered in the table and correcting strings doesn't make the formula easier at all and .