Help required with VLOOKUP for partial cell match

GLOpro

Board Regular
Joined
Jul 30, 2005
Messages
113
Hi,

How would i go about setting up a VLOOKUP for a partial sequence:
ie. vlookup 249044311 where the array would show A0249044311.

thanks in advance!
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

DebugGalpin

Board Regular
Joined
Jun 29, 2011
Messages
175
Could use an index match formula if you need the look up.

to find the row checking column A (change that as required)
= Match("*249044311*",A:A,0)

=Index( array, match formula above, column number you want)
 

GLOpro

Board Regular
Joined
Jul 30, 2005
Messages
113
Hi DebugGalpin,

thanks for your reply.

I dont understand how to implement your suggestion.

Sheet 1, cell D2 is 249044311
check array on Sheet 2 B1:B50 till it finds A0249044311
then insert corresponding Column Number 12 data into Sheet1 Col 2

I hope I havent confused the outline further
 

DebugGalpin

Board Regular
Joined
Jun 29, 2011
Messages
175
No worries I've changed it up to go in cell B2 sheet 1

=INDEX(Sheet2!$1:$1048576,MATCH("*"&D2&"*",Sheet2!$B:$B,0),12)

First part of the Index is the array, I am assuming you have data in sheet2
Second part I match a wild card of cell D2 to Sheet 2 column B to give me a row number
Third part I return column 12 as column number

B2 equals Sheet 2 Cell (L whatever the row number is)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,337
Messages
5,601,026
Members
414,422
Latest member
acegreen

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