Search Lookup query

[rajtak]

Hello Everyone,

Please refer to the sample spreadsheet below and let me know your suggestions.

https://skydrive.live.com/view.aspx?cid=3FD0284A9C0EDDF0&resid=3FD0284A9C0EDDF0!191&app=Excel&wdo=1

1) I want to search every part number listed in column C on Column A and pull the image file name wherever the part number exist within the name
2) Image file names are not exact match so a straight forward vlookup won't work here
3) It might also be possible that a same part number exist within multiple image file names, so the first occurrence of search must be pulled in Column D, 2nd occurrence in Column E and so on.

Thank you in advance for your help.


Regards,
Raj

 

[rajtak]

Can anyone really help me on this?

 

[Aladin Akyurek]

Can anyone really help me on this?

Would you post here a 5 row sample, i.e., the input, along with the results you want to see, i.e., the output?

 

[rajtak]

Hi Aladin,

Please open this link, I've the desired output here.

https://skydrive.live.com/view.aspx?cid=3FD0284A9C0EDDF0&resid=3FD0284A9C0EDDF0!191&app=Excel&wdo=1

 

[azumi]

Dear Raj

Im already givin you the answer, dont try to double post... please check the links

Search Lookup Index query help

Regards

 

[Aladin Akyurek]

Hi Aladin,

Please open this link, I've the desired output here.

https://skydrive.live.com/view.aspx?cid=3FD0284A9C0EDDF0&resid=3FD0284A9C0EDDF0%21191&app=Excel&wdo=1

Just posting a small sample here would be wonderful, really.

 

[rajtak]

Dear Raj

Im already givin you the answer, dont try to double post... please check the links

Search Lookup Index query help

Regards

Your formula works like a charm but only with the format I have created. If I want to search the part numbers in column A entirely (without specifying number of rows i.e. $A:$A) and there is no blank column like column B then what changes do i have to make to make it work? Please suggest.

 

[rajtak]

Just posting a small sample here would be wonderful, really.

A	B	C	D	E	F	G
			Occurrences
Image File Names		Part Numbers	1	2	3	4
b1011Back.jpg		1011	b1011Back.jpg	a1011Angle.jpg	wsd1011.jpg
sdw1020.jpg		1012	1012.jpg	aba1012Angled.jpg	a1012Row.jpg
1017_4.jpg		1013	a1013.jpg	1013Loose.jpg
1014Front.jpg		1014	1014Front.jpg	as1014.jpg
1019.jpg		1015	1015.jpg	bs1015_Back.jpg
awew1018_2.jpg		1016	awew1018_2.jpg	sew1016_Back.jpg	1016.jpg
bww1019_Row.jpg		1017	1017_4.jpg	a1017.jpg	asea1017_Upside.jpg	aasd1017_Back.jpg
b1016_Inside.jpg		1018	awew1018_2.jpg	sd1018.jpg
1012.jpg		1019	1019.jpg	bww1019_Row.jpg	c1019_4.jpg	aewa1019_Upfront.jpg
a1013.jpg		1020	sdw1020.jpg
sew1016_Back.jpg
a1017.jpg
asea1017_Upside.jpg
1015.jpg
1013Loose.jpg
aba1012Angled.jpg
sd1018.jpg
bs1015_Back.jpg
a1012Row.jpg
c1019_4.jpg
a1011Angle.jpg
as1014.jpg
wsd1011.jpg
1016.jpg
aasd1017_Back.jpg
aewa1019_Upfront.jpg

<tbody>
</tbody>

I need a dynamic formula that is not specific to this format only.

 

[azumi]

Just extend A$A$3:$A$28 to $A$3:$A$200000 that will give you rows more than enaough I guess, and don't worry about "COLUMNS($B$1:B1)" part..... that is just count Columns...

 

[Aladin Akyurek]

A	B	C	D	E	F	G
			Occurrences
Image File Names		Part Numbers	1	2	3	4
b1011Back.jpg		1011	b1011Back.jpg	a1011Angle.jpg	wsd1011.jpg
sdw1020.jpg		1012	1012.jpg	aba1012Angled.jpg	a1012Row.jpg
1017_4.jpg		1013	a1013.jpg	1013Loose.jpg
1014Front.jpg		1014	1014Front.jpg	as1014.jpg
1019.jpg		1015	1015.jpg	bs1015_Back.jpg
awew1018_2.jpg		1016	awew1018_2.jpg	sew1016_Back.jpg	1016.jpg
bww1019_Row.jpg		1017	1017_4.jpg	a1017.jpg	asea1017_Upside.jpg	aasd1017_Back.jpg
b1016_Inside.jpg		1018	awew1018_2.jpg	sd1018.jpg
1012.jpg		1019	1019.jpg	bww1019_Row.jpg	c1019_4.jpg	aewa1019_Upfront.jpg
a1013.jpg		1020	sdw1020.jpg
sew1016_Back.jpg
a1017.jpg
asea1017_Upside.jpg
1015.jpg
1013Loose.jpg
aba1012Angled.jpg
sd1018.jpg
bs1015_Back.jpg
a1012Row.jpg
c1019_4.jpg
a1011Angle.jpg
as1014.jpg
wsd1011.jpg
1016.jpg
aasd1017_Back.jpg
aewa1019_Upfront.jpg

<tbody>
</tbody>

I need a dynamic formula that is not specific to this format only.

Let Sheet1, A:A, house the data.

Define ImageFiles by means of Formulas | Name Manager as referring to:

=Sheet1!$A$3:INDEX(Sheet1!$A:$A,MATCH(REPT("z",255),Sheet1!$A:$A))

C3, control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX(ImageFiles,
  SMALL(IF(ISNUMBER(SEARCH($C3,ImageFiles)),
  ROW(ImageFiles)-ROW(INDEX(ImageFiles,1,1))+1),COLUMNS($D3:D3))),"")

The set up is dynamic... By the way, you can delete the empty column B if so desired.