Database lookup

[Beatrice]

I would like to create a table for user to look for the most similar Product Description.

Let say, they need to input 3 criteria:

1. text from a drop down list << need exact match
2. text from a drop down list << need exact match
3. number only << FIVE of the nearest match

* database will be stored as separated worksheets and locked for access control
* Excel version 2007

I tried below, but not working:
1. Data> Advance Filter
2. ARRAY Formula (index, match ...)

How can I make it happened?
Many thanks in advance for helping

 

[AhoyNC]

See my reply to your original post.

How to Extract list from Database

I would like to get some help for extracting part of the database to a new selection list. Please see my file as link below: WeTransfer download file Many thanks in advance for your help. Bea

www.mrexcel.com

 

[Beatrice]

Thanks Ahoy, however the requirement is more difficult than yesterday...
I need to look for the nearest FIVE value.

Please see the example I created:

I am trying to understand how it works as the original database file are upto 100+ Column & 500+ row

example workbook

Thanks in advance for helping

 

[AhoyNC]

Maybe something like this:
Formula in B4 counts the number of items that match the users criteria. In your example 2 there are actual 6 items that match.
The formulas in Sheet2 cells B3, B4 and G10 are all array formulas that must be entered with CTRL-SHIFT-ENTER.
Drag the formula in G10 down the number of rows needed for your data.


Copy formula in D10 across and down as needed.

Sheet2

Book1
	A	B	C	D	E	F	G
1	Example 2
2
3	Max Diff. of Top 5	15
4	# of Items	6
5
6
7	User input:			result:
8
9	TYPE	VALUE		TYPE	BRAND	VALUE	Row#
10	Type B	90		Type B	Brand 1	80	11
11				Type B	Brand 1	100	12
12				Type B	Brand 2	75	15
13				Type B	Brand 2	85	16
14				Type B	Brand 2	95	17
15				Type B	Brand 2	105	18
Sheet2

Cell Formulas
Range	Formula
B3	B3	{=SMALL(IF(Sheet1!$A$8:$A$26=$A$10,ABS(Sheet1!$C$8:$C$26-$B$10)),5)}
B4	B4	{=SUM(--(ISNUMBER(IF(Sheet1!$A$8:$A$26=$A$10,IF(ABS(Sheet1!$C$8:$C$26-$B$10)<=$B$3,ROW(Sheet1!$C$8:$C$26)-ROW(Sheet1!$C$8)+1)))))}
D10:F15	D10	=IF($G10="","",INDEX(Sheet1!$A$8:$C$26,$G10,MATCH(D$9,Sheet1!$A$7:$C$7,0)))
G10:G15	G10	{=IF(ROWS($G$10:G10)>$B$4,"",SMALL(IF(Sheet1!$A$8:$A$26=$A$10,IF(ABS(Sheet1!$C$8:$C$26-$B$10)<=$B$3,ROW(Sheet1!$C$8:$C$26)-ROW(Sheet1!$C$8)+1)),ROWS($G$10:G10)))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Sheet1

Book1
	A	B	C
1
2
3
4	Database
5
6
7	TYPE	BRAND	VALUE
8	Type A	Brand X	210
9	Type A	Brand X	230
10	Type A	Brand X	250
11	Type A	Brand X	300
12	Type A	Brand Y	200
13	Type A	Brand Y	250
14	Type A	Brand Y	300
15	Type A	Brand Z	240
16	Type A	Brand Z	260
17	Type A	Brand Z	280
18	Type B	Brand 1	80
19	Type B	Brand 1	100
20	Type B	Brand 1	120
21	Type B	Brand 1	150
22	Type B	Brand 2	75
23	Type B	Brand 2	85
24	Type B	Brand 2	95
25	Type B	Brand 2	105
26	Type B	Brand 2	115
Sheet1

 

[Beatrice]

Thanks Ahoy! You are a life saver !
I have modified B3 as variable , so user can set the "Tolerance" and get the closest results.
Many thanks for your help

 

[AhoyNC]

You're welcome. Thanks for the feedback.