Move to next cell with Vlookup and Left function

[topswim]

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

 

[jasonb75]

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]

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
	A	B	C	D	E
1	ADGLHRH1	data 1		ADGLH	data 1
2	XBCDEF	data 2			data 3
3	ADGLHQ0P	data 3			data 6
4	QWERTYU	data 4			data 8
5	YTREWQ	data 5
6	ADGLHW	data 6
7	ZXCVBNM	data 7
8	ADGLHSWID9	data 8
Filter

Cell Formulas
Range	Formula
E1:E4	E1	=FILTER(B1:B8,LEFT(A1:A8,LEN(D1))=D1,"")
Dynamic array formulas.

 

[topswim]

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
	A	B	C	D	E
1	ADGLHRH1	data 1		ADGLH	data 1
2	XBCDEF	data 2			data 3
3	ADGLHQ0P	data 3			data 6
4	QWERTYU	data 4			data 8
5	YTREWQ	data 5
6	ADGLHW	data 6
7	ZXCVBNM	data 7
8	ADGLHSWID9	data 8
Filter

Cell Formulas
Range	Formula
E1:E4	E1	=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]

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

=FILTER(A1:A8,LEFT(A1:A8,LEN(D1))=D1,"")

Also please do not forget to update your account details.

 

[topswim]

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

=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]

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]

Just change the value in D1

 

[topswim]

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]

Firstly, can you please update your account details as requested.
Secondly you will need to explain exactly what you are trying to do.