VLOOKUP search for items with variable ending

[heinrij1]

Hi everyone

I have the following problem. I would like to do a VLOOKUP since I have a list with parts in one file. And a list with parts and reference numbers in another list. I would like to associate the reference numbers to the parts in the first list.

The part numbers have the following format: 1234 AAA

Problem is: The part numbers may vary at the end. You would have ABA when the part was slightly altered. BAA would be a completetly new development of the part. Thus I would lose my match.

Therefore, I would have to search first for the exact part number and then reduce to search for 1234 AA and if there is no match saerch for 1234 A. Of course since the can also be sveral hits for 1234 A like 1234 AAA or 1234 ABA e.g. I would have to display all hits.

Anyone know how to do this?

Thank you for your help!

[Glenn Kennedy]

Hi there... and welcome to the Excel Forum. Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[AlKey]

Hi heinrij1 and welcome to the forum,

Here is an example of how this can be achieved by using array formulas.

Assuming the listed part numbers are in column D and a partial lookup value located in cell B2. The formula to retrieve matched items is in column A.

Since your profile shows that you using Excel 2010 version or up you can take advantage of using formula with AGGREGATE function

=IFERROR(INDEX(D:D,AGGREGATE(15,6,ROW($D$2:$D$5)/((LEFT($D$2:$D$5,FIND(" ",$D$2:$D$5)+1)=$B$2)),ROWS(D$2:D2))),"") 

or another version that will be compatible with older excel versions prior to 2010.

=IFERROR(INDEX(D:D,SMALL(IF(LEFT($D$2:$D$5,FIND(" ",$D$2:$D$5)+1)=$B$2,ROW($D$2:$D$5)),ROWS(D$2:D2))),"") 

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

Row\Col	A	B	C	D
1	Search Result	Lookup value		List of Part #
2	1234 KJA	1234 K		1234 KJA
3	1234 KJB			1234 AAB
4				1234 ACA
5				1234 KJB