Move to next cell with Vlookup and Left function

topswim

Board Regular
Joined
May 14, 2002
Messages
128
Office Version
  1. 365
Platform
  1. Windows
This is what I'm trying to do. I want to look up THE FIRST 5 characters of a cell against another sheet which has multiple names with thIS first five characters. What I would like to do is once it finds the first match that I can THEN move to get the second match etc. Below is an example of what I'm looking at

Sheet1 A1- ADGLH

Reference sheet:

A1=ADGLHRH1
A2=ADGLHQ0P
A3=ADGLHW
A4=ADGLHSWID9

This is Microsoft Excel for Microsoft 365 MSO ( 16.0 dot 13929.20406) 64 bit
Excel 2014 Build 13929.20408
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,559
Office Version
  1. 365
Platform
  1. Windows
Vlookup (or any lookup type function for that matter) can only ever return the first match (or last with xlookup). For multiple matches you need to use array methods, as you have office 365 the FILTER() function would be the best option.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,831
Office Version
  1. 365
Platform
  1. Windows
This is Microsoft Excel for Microsoft 365
I suggest that you update your Account details (or click your user name at the top right of the forum) with the above information so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Is it something along these lines that you are looking to do?

21 10 07.xlsm
ABCDE
1ADGLHRH1data 1ADGLHdata 1
2XBCDEFdata 2data 3
3ADGLHQ0Pdata 3data 6
4QWERTYUdata 4data 8
5YTREWQdata 5
6ADGLHWdata 6
7ZXCVBNMdata 7
8ADGLHSWID9data 8
Filter
Cell Formulas
RangeFormula
E1:E4E1=FILTER(B1:B8,LEFT(A1:A8,LEN(D1))=D1,"")
Dynamic array formulas.
 

topswim

Board Regular
Joined
May 14, 2002
Messages
128
Office Version
  1. 365
Platform
  1. Windows
I suggest that you update your Account details (or click your user name at the top right of the forum) with the above information so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Is it something along these lines that you are looking to do?

21 10 07.xlsm
ABCDE
1ADGLHRH1data 1ADGLHdata 1
2XBCDEFdata 2data 3
3ADGLHQ0Pdata 3data 6
4QWERTYUdata 4data 8
5YTREWQdata 5
6ADGLHWdata 6
7ZXCVBNMdata 7
8ADGLHSWID9data 8
Filter
Cell Formulas
RangeFormula
E1:E4E1=FILTER(B1:B8,LEFT(A1:A8,LEN(D1))=D1,"")
Dynamic array formulas.
Thank you. This looks pretty close, but I am not understanding the B cell range as I only want all the matches in A
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,938
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

The reference to col B, was because Peter was guessing at your requirement, as it was not very clear.
If you just want to look at column A, try
Excel Formula:
=FILTER(A1:A8,LEFT(A1:A8,LEN(D1))=D1,"")

Also please do not forget to update your account details. ;)
 

topswim

Board Regular
Joined
May 14, 2002
Messages
128
Office Version
  1. 365
Platform
  1. Windows
The reference to col B, was because Peter was guessing at your requirement, as it was not very clear.
If you just want to look at column A, try
Excel Formula:
=FILTER(A1:A8,LEFT(A1:A8,LEN(D1))=D1,"")

Also please do not forget to update your account details. ;)
Thank you Fluff. I thought it was pretty straight forward though since I was not involving another column. I will try this though
 

topswim

Board Regular
Joined
May 14, 2002
Messages
128
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thank you Fluff. I thought it was pretty straight forward though since I was not involving another column. I will try this though
ok, I can get this to work. Now is there a way I can go right to the next five 5 characters (ALDHI) after I have found everything for ADLGH.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,938
Office Version
  1. 365
Platform
  1. Windows
Just change the value in D1
 

topswim

Board Regular
Joined
May 14, 2002
Messages
128
Office Version
  1. 365
Platform
  1. Windows
Just change the value in D1
yes I can change it manually I'm trying to see if it can automatically go do the next 5 characters in the list or any other recommendations to simplify the process, as I have 1000 different 5 characters I need to do this with referencing this same column
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,938
Office Version
  1. 365
Platform
  1. Windows
Firstly, can you please update your account details as requested.
Secondly you will need to explain exactly what you are trying to do.
 

Forum statistics

Threads
1,148,154
Messages
5,745,097
Members
423,922
Latest member
Taconumber

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
Top