A tweak to a VLOOKUP formula required

damian_r_Home

Board Regular
Joined
Jan 8, 2005
Messages
231
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
Af'noon All.

Earlier today I put a post (Some sort of lookup function?) which was resolved using this formula

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

If you have a look at the original thread, you will see that it is to do with a partial text string.

I'm now looking for a tweaked version of this formula so that it will pick up the first part of the text string...
1628606988214.png

Hopefully somebody can help with this.

Regards

D
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
What is the first part of the text string, everything before the first space?
 
Upvote 0
Hi Fluff - I should've known you'd be along as usual to help.

The first part of the string that forms the search criteria is "BH-E - Terracotta"
1628611964912.png

The format will always be the same as in 2letters hyphen letter space hyphen space word

Hope that helps
 
Upvote 0
Yes, but what you consider to be the 1st part of the string?
 
Upvote 0
Morning.

The phrase it needs to search for from the text example of "BH-E - Terracotta - Helping Hands" is "BH-E - Terracotta"

The input text will vary, such as "HH-W - Render - Flashings" of which the part the formula needs to look up would be "HH-W - Render"

The format of this first part to look up will always be the same as in 2letters hyphen letter space hyphen space word
 
Upvote 0
Ok, how about
Excel Formula:
=LEFT(C6,FIND("^",SUBSTITUTE(C6," -","^",2))-1)
 
Upvote 0
Morning Fluff.

Apologies for not getting back to you in this but I had a few days off of work and the Mrs would have killed me if I'd been looking at work stuff.

I've had a go at using your formula and I can get it to do what its supposed to do..is it possible to add a lookup to it so that the phrase it currently finds it then looks for in range Sheet1!$B$6:$C$120?
 
Upvote 0
How about
Excel Formula:
=vlookup(LEFT(C6,FIND("^",SUBSTITUTE(C6," -","^",2))-1),Sheet1!$B$6:$C$120,2,0)
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top