VLOOKUP with contains function?

**DannyG** · 02-22-2008, 06:23 AM

Does anyone know if the following is possible?

I have a column of data with fields that look like ABCD1234RTF or AB-CRT3456-PRT.

I then have a table with the values 1234 and 3456 etc in it with corresponding other data.

Is there a function I can use to search through my list of data looking within each field for just the number part and if found then return another column from my lookup table?

I hope for 2 things:
1) that what I have written actually makes sense to someone
2) that someone knows how I can achieve the above result

Thanks

**sweep** · 02-22-2008, 07:34 AM

Hi,

You can extract just the numeric portion of your cell with this array formula

=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))

enter it with CTRL, SHIFT and ENTER and excel should display it like this

{=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))}

You can then use the number to do the lookup.

**starguy** · 02-22-2008, 07:37 AM

You can try using lookup_value wraped with wildcard like this: *1234*

Formula would be like this

=VLOOKUP("*1234*",table_array,col_index,0)

**DannyG** · 02-22-2008, 08:54 AM

Hi,

The formula you stated to pull out the numerical part is really good (though I have no idea how it works!). Unfortunately it does not work on all of my records. For example see the records below:

Would it be possible to use a variation of the formula to pick out the 800903 from ABC9-D800903EFG-H?

I need to pick out the 98355 from E8BG-98355X023
I need to pick out the 98351 from F182-98351J789

Thanks

**starguy** · 02-25-2008, 05:48 AM

Originally Posted by DannyG

Hi,

The formula you stated to pull out the numerical part is really good (though I have no idea how it works!). Unfortunately it does not work on all of my records. For example see the records below:

Would it be possible to use a variation of the formula to pick out the 800903 from ABC9-D800903EFG-H?

I need to pick out the 98355 from E8BG-98355X023
I need to pick out the 98351 from F182-98351J789

Thanks

Have you tried formula using wildcard?

**DannyG** · 02-27-2008, 05:04 AM

Hey,

Yeah I did try this and it works really well. it does not do exactly as I need it too but I can work with it to achieve the desired effect!

Thanks

Thread: VLOOKUP with contains function?

Thread Tools

Display

VLOOKUP with contains function?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions