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

 

[topswim]

OK I have updated the account details to the best of my knowledge. Here is what I'm trying to do:

partial list of the 1000 5 letter abbr I am using to find other values of the same first five characters but different last 4 to 10 characters etc

ALAUB

ALBHM

ALBIR

ALDEE

ALFOL

ALHOO

ALMOB

using above to reference list below for all cells in this column that match the above criteria

Part of the 2990 below I am looking against

AKANCC05

AKANCC07

AKANCC08

AKFAIQ1

ALAUB12E02

ALBHM13EB1

ALBHM13EB2

ALBHM14EQ1

ALBHM14EQ2

ALBHMC01

ALBHMC02

ALBHMCG1

ALBHMCG2

ALBIRD1

for example I can perform the following line you gave me with ALBHM and return all the ones in this above list. When it's done with that I wanted to look up the next cell and anything associated with ALBIR, etc.

Let me know if I need to clarify even further but hopefully this is enough to see what I'm doing

 

[jasonb75]

Is this what you want?

Book1
	A	B	C	D	E
1	ALAUB		AKANCC05		ALAUB12E02
2	ALBHM		AKANCC07		ALBHM13EB1
3	ALBIR		AKANCC08		ALBHM13EB2
4	ALDEE		AKFAIQ1		ALBHM14EQ1
5	ALFOL		ALAUB12E02		ALBHM14EQ2
6	ALHOO		ALBHM13EB1		ALBHMC01
7	ALMOB		ALBHM13EB2		ALBHMC02
8			ALBHM14EQ1		ALBHMCG1
9			ALBHM14EQ2		ALBHMCG2
10			ALBHMC01		ALBIRD1
11			ALBHMC02
12			ALBHMCG1
13			ALBHMCG2
14			ALBIRD1
Sheet5

Cell Formulas
Range	Formula
E1:E10	E1	=SORT(FILTER(C1:C14,ISNUMBER(MATCH(LEFT(C1:C14,5),A1:A7,0)),""))
Dynamic array formulas.

 

[topswim]

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

I updated the account details.
Really the only issue I'm having is when I drag the formula down I get a spill, which is due to the next value containing more than one value


Yes this looks like it may work Jason. I will try and let you know. Thanks

 

[topswim]

I updated the account details.
Really the only issue I'm having is when I drag the formula down I get a spill, which is due to the next value containing more than one value


Yes this looks like it may work Jason. I will try and let you know. Thanks

Perfect!!, Thank you both