Array formula to lookup for values and create a data Validat

[lovallee]

Hello:

I am trying to use and array formula to extract certain values from a database and return a list of extracted value which will be use later for data Validation list (tools / Validation / list). Here is an example of what I tried so far:

Classeur1.xls
	A	B	C	D
1	TheData:
2	Database
3	Field1 (Scannedvalues)	Field2-option1 (valuetobereturnedtotheValidationlist)	Field2-option2 (valuetobereturnedtotheValidationlist)
4	Hello	1013	S345s
5	Bye	204	10-10-34-409
6	Goodbye	4056	a1D45
7	Hello	234	g56jJ
8	Goodmorning	5600	d459s
9	Hello	4555	1jsj8-js2
10
11	Lookupvalue(rangecreateonlyforarrayformulapurpose
12	Hello
13	Hello
14	Hello
15	Hello
16	Hello
17	Hello
18
19	Progresstodate:
20	RangeofarraysformulathatcreatestheValidationlist
21	Formula	Valuereturnedbytheformula
22	{=LARGE(IF(EXACT(B$5:$B$10;$D$5:$D$10);OFFSET($D$5:$D$10;0;1;;););1)}	4555
23	{=LARGE(IF(EXACT(B$5:$B$10;$D$5:$D$10);OFFSET($D$5:$D$10;0;1;;););2)}	1013
24	{=LARGE(IF(EXACT(B$5:$B$10;$D$5:$D$10);OFFSET($D$5:$D$10;0;1;;););3)}	234
25	{=LARGE(IF(EXACT(B$5:$B$10;$D$5:$D$10);OFFSET($D$5:$D$10;0;1;;););4)}	0
26	..
27	{=LARGE(IF(EXACT(B$5:$B$10;$D$5:$D$10);OFFSET($D$5:$D$10;0;1;;););n)}	0
28
Feuil1

Problems:
1 - The above template only works with numeric data to be returned (field2)
2 - I need to copy the "Lookup value" in a range of the same size than the "Scanned values" range

Needed:
1 - I need to return text value to the data Validation list (as in Field2 - option 2).
2 - I would prefer not to have to create a range for the Lookup value.
The Lookup value need to be in one cell only in order to simplify the template.
3 - How can I do that???

Thank you !

Louis

 

[lovallee]

Forget about the present message,

Its my first try with Colo's add-in

Louis