Vlookup - Partial Match

**VegasL** · 06-05-2008, 12:53 PM

Hello,

Im trying to do vlookup between 2 columns, i.e. If Column C Info (Partial Match) Is Not On Column A, then Display Result In Column F (where it wasn't a partial match), Else Leave Blank

If there is a partial match, display nothing.

Please see attached spreadsheet.

The formula I tried, in column I is obviously not giving desired result.
=IF(ISNA(VLOOKUP(C2,$A$2:$A$5,1,TRUE)),"-",(VLOOKUP(C2,$A$2:$A$5,1,TRUE)))

**NBVC** · 06-05-2008, 01:05 PM

Try:

=IF(ISNA(VLOOKUP("*"&C2&"*",$A$2:$A$5,1,0)),"-",VLOOKUP("*"&C2&"*",$A$2:$A$5,1,0))

or

=IF(ISNA(MATCH("*"&C2&"*",$A$2:$A$5,0)),"-",MATCH("*"&C2&"*",$A$2:$A$5,0))

**VegasL** · 06-05-2008, 01:52 PM

thanks nbvc for your help, appreciate your time

however, would like it to return all values in column c, which are not in column a, where column c and and column A have a partial match.

see column f on spreadsheet, for actual desired result.

sorry if i wasnt clear first time.

**VegasL** · 06-05-2008, 02:13 PM

my bad thought i figured it out but not quite yet...so close..dang

**NBVC** · 06-05-2008, 03:56 PM

Something like:

=IF(ISNUMBER(MATCH(C2,LEFT($A$2:$A$5,LEN(C2)),0)),"-",C2)

confirmed with CTRL+SHIFT+ENTER not just ENTER and then copied down...

**VegasL** · 06-06-2008, 06:07 PM

Hi NBV5,

I uploaded new spreadsheet (please see attachment), comparing tab b to tab a, with results in tab c, cell a1, but am getting all "-"'s, using

=IF(ISNA(VLOOKUP("*"&B!A1&"*",A!$A$2:$A$5,1,0)),"-",VLOOKUP("*"&B!A1&"*",$A$2:$A$5,1,0))

the actual desired result is listed on tab c, which is displaying where the partial match was not found.

Thanks in advance for any continued help you may be able to provide.

**VegasL** · 06-07-2008, 01:58 PM

...............anyone?

**Shijesh Kumar** · 06-07-2008, 02:14 PM

Hi try this formula

Please Login or Register  to view this content.

* Dont put curly braces...
* Dont presss enter , instead press ctrl+shift+enter.. curly brace automatically appear... if curly brace does not appear after pressing ctrl+shift+enter then forumla will not work

**VegasL** · 06-08-2008, 03:25 AM

Hi Shijesh,

Yes it works for the most part. Thanks. I am a bit confused perhaps you can clarify...

ok that was sample sheet i posted which in tab a had 5 rows. In actual i have about 299 rows and in tab b, i have 320 rows

thus I modified your formula to read, i.e. =IF(ISNA(VLOOKUP(B!B2,LEFT(A!$A$2:$A$299, LEN(B!B320)),1,0)),B!B320,"-")

but it retuns a "-" for all values, when I change it back to A!$A$2:$A$5, it returns results but naturally it wouldnt be doing a vlookup on. I noticed when I just change a value in formula, it removes the {} braces.

anyhow, put formula with = sign first, then pressed Ctrl+Shift+Enter, and it put curly braces in.

Question - I've never seen this functionaly of curly braces before. What do they do, - where can I find out more about them, why doesnt formula work without them

I understand most of your formula, upto ={IF(ISNA(VLOOKUP(B!A2,

But dont understand, LEFT(A!$A$2:$A$5, LEN(B!A2))

I know left function has char, text

could you perhaps explain your formula in plain english,ie.

ie. lookup cell a2 in this array, but if its true its not there (IF(ISNA(, then put ,B!A2 otherwise put -, hence ,"-" something like that if you wouldnt mind.

Again thanks to both of you for putting your valuable time to helping me out. Sincerely appreciate it.