match with multiple criteria to a dataset of unique values

[DutchKevin]

Hi All,

I’d like to post the following question.
In one sheet I’d like the user to select from 3 dropdown lists certain predefined values.
On the second sheet there is a long list of unique cells (one column, that can't be split into usefull columns with text to columns or something).
I want to find the cell that holds the three text choices. These can be in different order to make things more complex.
How do I create a search that finds that one match.

Example
The user selects “AAA” and “DDD” and “FFF” from the dropdown lists
The formula should find that one cell that holds this value: “FFF JJJ GGG DDD CCC AAA”. This is the only cell that holds all three chosen values in one text. In the end I would need to have the row number of that cell, but either way I would be happy to find the answer.

 

[Yahya]

please check if this will help you

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K
1	Cri 1	Cri 2	Cri 3	Val			Drop 1	Drop 2	Drop 3
2	AAA	BBB	CCC	5134			CCC	AAA	BBB		2002
3	BBB	BBB	CCC	1756
4	CCC	BBB	CCC	4985
5	AAA	BBB	CCC	4879
6	AAA	BBB	CCC	2929
7	AAA	BBB	CCC	1820
8	AAA	BBB	CCC	2726
9	CCC	BBB	CCC	4069
10	CCC	BBB	CCC	4996
11	CCC	BBB	BBB	1767
12	CCC	AAA	BBB	2002
13	BBB	AAA	CCC	4085
14	BBB	AAA	CCC	565
15	BBB	AAA	CCC	4146
16	BBB	AAA	CCC	2658
17	BBB	AAA	CCC	2367
Sheet1

 

[DutchKevin]

Hi Yahya, thanks for the quick reply.
My reference data is however not so neatly organized as it now is in your model. It occupies only one column, and I can't split it over multiple columns.
What I would nead is more like searching multiple text occurances in a string.
I do wonder how you come to the Val column?

Kevin

 

[Yahya]

could you post small sample from your data please

 

[DutchKevin]

Hi,
this is part of it.
my user would for instance select "bottom cover" "3503" and "1390", resulting in the bold text to be preseneted as outcome.
but many, many more different text can be searched for. some 35 choices need to be made in the end. searching through the range for the one that matches.[TABLE="width: 399"]
<tbody>[TR]
[TD="align: left"][/TD]
[/TR]
[TR]
[TD="align: left"]Top Cover-1510-no split[/TD]
[/TR]
[TR]
[TD="align: left"]Top Cover-2500/2510-split[/TD]
[/TR]
[TR]
[TD="align: left"]Top Cover-3503-split[/TD]
[/TR]
[TR]
[TD="align: left"]Top Cover-3510-split[/TD]
[/TR]
[TR]
[TD="align: left"]Bottom Cover-1510-900[/TD]
[/TR]
[TR]
[TD="align: left"]Bottom Cover-2500/2510-900[/TD]
[/TR]
[TR]
[TD="align: left"]Bottom Cover-3503-900[/TD]
[/TR]
[TR]
[TD="align: left"]Bottom Cover-3503/3510 - 1390[/TD]
[/TR]
[TR]
[TD="align: left"]Bottom Cover-3503-1625[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[DutchKevin]

Hi, I'm back online again, eager to solve this

 

[DutchKevin]

Re: match with multiple criteria to a dataset of unique values, continued

ok, i seem to have half the solution.

=AND(SEARCH($H$2:$H$8;B2))

this compares all criteria in H2:H8 against the content of B2.
that's good. I could use it to run a helper column to support my needs. this would unfortunately lead to too many columns to my liking.

Is there a way to let the array formula present me a relative position in the data range at which the match is found?
Suppose the range is A1:A500, and the match is at position of row 243, can the array present that as an outcome?

 

[DutchKevin]

Re: match with multiple criteria to a dataset of unique values, continued


oops, double submitted