Index Match /Vlookup issue

DeaninGA

Board Regular
Joined
May 20, 2013
Messages
141
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
So I have a column of data that has various part numbers in. (CVS8H900b2030HILE3) for instance. I need to extract the HILE3 part and match it to a Vlookup style table to return an extended description of that part. The issue is its not always HILE3 it could be some other series of letters and it might e 1 or 2 characters more or less than the HILE3... I have a Vlookup style table with the complete list on a support tabe with the answers I want it to return. Any help would be greatly appreciated.

DD
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
Let A2 house
CVS8H900b2030HILE3.

Let F2:G100 house the lookup table.

In B2 enter (and copy down if you have other values to look up)...

=LOOKUP(9.99999999999999E+307,SEARCH($F$2:$F$100,$A2),$G$2:$G$100)
 

DeaninGA

Board Regular
Joined
May 20, 2013
Messages
141
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Let A2 house
CVS8H900b2030HILE3.

Let F2:G100 house the lookup table.


In B2 enter (and copy down if you have other values to look up)...

=LOOKUP(9.99999999999999E+307,SEARCH($F$2:$F$100,$A2),$G$2:$G$100)
Morning,

Thank you for the Quick Response. I tried it and getting an Error Here is My formula and an explanation

=LOOKUP(9.99999999999999E+307,SEARCH(RBC!A1:A96,[Description],RBC!B2:B96))

RBC!A1:A96 if the first column of a support table that contains the snippets of data im trying to extract from the part numbers in the "Description" column of a data table( represented by the PN I originally gave you), "Description" is the name of the column I'm trying to pull the data into. RBC!B2:B96 is the result Im trying to pull into the cell where this formula resides... I know I'm doing something wrong. thank you again
DD
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
The ranges are not equally sized. Set them equal in length. Does this resolve the issue?

=LOOKUP(9.99999999999999E+307,SEARCH(RBC!A1:A96,[Description],RBC!B1:B96))
 

Watch MrExcel Video

Forum statistics

Threads
1,128,173
Messages
5,629,142
Members
416,366
Latest member
ChrisDXB1

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