Matching Cell Content to Partial String

[SteveOranjin]

Hello,

I am attempting to use VLOOKUP to match the partial component of a text string. I cannot make it work presently.

What I’m looking to do is, match the image name from “Image Name” (A) Table 1 to “SKU” (C) in table 2 and based off of the match there, populate the corresponding information from image name into “Image”(D)

Table 1 and Table 2 are on two different tabs.


Table 1

Imagename (A)	Description (B)
Bagetelle_1238-48H_BLK_1x1HR_BG_300PX.JPG
Bagatelle_1239-22_B_300PX.JPG

<tbody>
</tbody>

TABLE 2

Brandname (A)	Brandecode (B)	SKU (C)	Image (D)
Snoopy	SNP	1238-48H	Target Cell

<tbody>
</tbody>

I believe this is a fairly simple problem to solve I'm just a little rusty right now with EXCEL.

 

[wideboydixon]

I had them on the same tab but hopefully this gives you a start:

Book1
	A	B	C	D
1	Imagename	Description
2	Bagetelle_1238-48H_BLK_1x1HR_BG_300PX.JPG	Desc 1
3	Bagatelle_1239-22_B_300PX.JPG	Desc 2
4
5
6
7	Brandname	Brandecode	SKU	Image
8	Snoopy	SNP	1238-48H	Bagetelle_1238-48H_BLK_1x1HR_BG_300PX.JPG
9	Charlie	CHR	1239-22	Bagatelle_1239-22_B_300PX.JPG
Sheet1

Cell Formulas
Range	Formula
D8	{=INDEX($A$1:$A$3,MIN(IF(ISNUMBER(FIND($C8,$A$1:$A$3)),ROW($A$1:$A$3)-ROW($A$1)+1)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[SteveOranjin]

That formula is just grabbing the contents of "ROW (A1)" for every it appears. are there specific sections I should be making changes to(Beyond the obvious references?)

The arrays I'm working with are A3192 - A4079. So My formula looks like this.

=INDEX($A$3192:$A$4079,MIN(IF(ISNUMBER(FIND($C2,$A$3192:$A$4079)),ROW($A$3192:$A$4079)-ROW($A$3192)+1))) (First cell)

=INDEX($A$3192:$A$4079,MIN(IF(ISNUMBER(FIND($C13,$A$3192:$A$4079)),ROW($A$3192:$A$4079)-ROW($A$3192)+1))) (A different cell further down that appears to grabbing the correct content.

 

[wideboydixon]

Formulas look OK. Did you enter them with Ctrl+Shift+Enter to create an array formula?

Can you post a snippet of the sheet where it's not working? Your original post said the tables were on different tabs but there's no indication of that in your formulas.

WBD

 

[SteveOranjin]

I moved it to one sheet to more closely emulate your formula.

https://ibb.co/cBNWpm

 

[SteveOranjin]

I have attached a link for an image twice and it says, "your post will not be posted until a moderator reviews it."

Sorry I have to post it this way.

https:
//
ibb.co/cBNWpm

 

[wideboydixon]

I suspect that those lines showing the same result over and over don't actually have a partial match in the lookup table and it's taking the first row from that table. If you add a top row called "Not Found" then it might make more sense?

WBD