VLOOKUP Partial Word Match

[Nanaia]

Greetings!
I am trying to use VLOOKUP to find paint codes using a partial match to the paint color. The painter doesn't list the number preceding the color, only the word name (Chromium Gray instead of 971 Chromium Gray). I've tried using VLOOKUP to automatically populate the code from the paint library (named Table 5) but keep getting an #N/A error. Most codes are numerical however the custom codes have a asterisk at the beginning (like *126). The formula I've tried is

=VLOOKUP("* "&D2,ISNUMBER(Table5[[Product Name]:[Code]]),2,0)

. Can anyone help me get this to work?

 

[jtakw]

Hi,

Is this what you mean, change range to your table name:

Book3.xlsx
	D	E	F	G	H
1
2	Chromium Gray	971 Chromium Gray			123 Red
3	Orange	*159 Orange			456 Yellow
4					789 Blue
5					971 Chromium Gray
6					*159 Orange
Sheet964

Cell Formulas
Range	Formula
E2:E3	E2	=LOOKUP(2,1/SEARCH(D2,H$2:H$6),H$2:H$6)

 

[jtakw]

Or may be you mean the 2nd sample below?

Book3.xlsx
	D	E	F	G	H	I
1
2	Chromium Gray	971 Chromium Gray			123 Red
3	Orange	*159 Orange			456 Yellow
4					789 Blue
5					971 Chromium Gray
6					*159 Orange
7
8
9
10	Chromium Gray	67890			123 Red	23456
11	Orange	78901			456 Yellow	34567
12					789 Blue	56789
13					971 Chromium Gray	67890
14					*159 Orange	78901
Sheet964

Cell Formulas
Range	Formula
E2:E3	E2	=LOOKUP(2,1/SEARCH(D2,H$2:H$6),H$2:H$6)
E10:E11	E10	=LOOKUP(2,1/SEARCH(D10,H$10:H$14),I$10:I$14)

 

[Nanaia]

Or may be you mean the 2nd sample below?

Book3.xlsx
	D	E	F	G	H	I
1
2	Chromium Gray	971 Chromium Gray			123 Red
3	Orange	*159 Orange			456 Yellow
4					789 Blue
5					971 Chromium Gray
6					*159 Orange
7
8
9
10	Chromium Gray	67890			123 Red	23456
11	Orange	78901			456 Yellow	34567
12					789 Blue	56789
13					971 Chromium Gray	67890
14					*159 Orange	78901
Sheet964

Cell Formulas
Range	Formula
E2:E3	E2	=LOOKUP(2,1/SEARCH(D2,H$2:H$6),H$2:H$6)
E10:E11	E10	=LOOKUP(2,1/SEARCH(D10,H$10:H$14),I$10:I$14)

@jtakw Thank you. Your second example is closer to what I'm trying to accomplish. The only difference (although I don't know how crucial) would be that the asterisk is in front of the code number not the paint color. I am still getting an #N/A error using the formula from your cell E10

=LOOKUP(2,1/SEARCH(D2,Table5[Product Name]),Table5[Code])

 

[jtakw]

Makes no difference where the asterisk is, as long as the paint name matches:

Book3.xlsx
	D	E	F	G	H	I
10	Chromium Gray	*67890			123 Red	23456
11	Orange	*78901			456 Yellow	34567
12	Pink	#N/A			789 Blue	56789
13					*971 Chromium Gray	*67890
14					159 Orange	*78901
Sheet964

Cell Formulas
Range	Formula
E10:E12	E10	=LOOKUP(2,1/SEARCH(D10,H$10:H$14),I$10:I$14)

If you are still getting errors, it's time to post some samples, preferably using XL2BB, link in my signature.

EDIT: The only reason I can see for getting #N/A, is it's Not on the list, like Pink above.

 

[Nanaia]

This is a work computer so I'm not comfortable downloading the XL2BB add-on.

The formula is on the DATA tab. The LIBRARY tab has the color and code library in Table5. The heading for the colors in the library is [Product Name] and the code is

 in the formula I shared.
Figure 1: Table5 (Paint Library)
[ATTACH type="full"]56291[/ATTACH]

Figure 2: DATA Tab (the only cell that has a formula is it is C2, all other data in column C is manually entered)
[ATTACH type="full"]56292[/ATTACH]

Figure 3: Listing of Chromium Gray in library

 

[jtakw]

I don't see "Chromium Gray" in your short list, perhaps use a paint name that IS on your list where the formula Errors out.

 

[jtakw]

Ok, I missed your "Figure 3" and only looked at the attached pics in you post # 6,

How about this, instead of using your Named ranges, use regular cell referencing in the formula to see if it Still errors out, worth a shot.

 

[Nanaia]

Your suggestion got me to look at the entry - it had an unneeded space at the end of it. I removed the space and it worked. Thank you!

 

[jtakw]

Your suggestion got me to look at the entry - it had an unneeded space at the end of it. I removed the space and it worked. Thank you!

You're welcome, thanks for letting me know.

Ah, that would be the same as if it wasn't on the list, thus #N/A.
I'm glad you sorted it out, and it's working for you now.