Move to next cell with Vlookup and Left function

topswim

Board Regular
Joined
May 14, 2002
Messages
131
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
 
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
 
Upvote 0

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Is this what you want?
Book1
ABCDE
1ALAUBAKANCC05ALAUB12E02
2ALBHMAKANCC07ALBHM13EB1
3ALBIRAKANCC08ALBHM13EB2
4ALDEEAKFAIQ1ALBHM14EQ1
5ALFOLALAUB12E02ALBHM14EQ2
6ALHOOALBHM13EB1ALBHMC01
7ALMOBALBHM13EB2ALBHMC02
8ALBHM14EQ1ALBHMCG1
9ALBHM14EQ2ALBHMCG2
10ALBHMC01ALBIRD1
11ALBHMC02
12ALBHMCG1
13ALBHMCG2
14ALBIRD1
Sheet5
Cell Formulas
RangeFormula
E1:E10E1=SORT(FILTER(C1:C14,ISNUMBER(MATCH(LEFT(C1:C14,5),A1:A7,0)),""))
Dynamic array formulas.
 
Upvote 0
Solution
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
 
Last edited by a moderator:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

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