HELP WITH VLOOKUP

[OVERPACKED]

HI,

I am trying to use VLOOKUP to auto fill data for me. I want the user of the sheet to type in part of the id number in the field and then have the rest of the data auto filled for him. The problem is that the data the user is typing in is not unique and Vlookup is just returning the 1st one in the list.

How do I get it to return a drop down list for the user to then select the best match??

CAN ANYONE HELP??

Thanks

 

[Lewiy]

I would do something like below. Column B is the list of values, column A is used to count each item which matches the partial search value entered in C1 and column D lists only the matching items.

You can then use data validation based on column D.

Excel Workbook
	A	B	C	D	E
1			mat
2	0	Deal1		Match1
3	0	Deal2		Match2
4	0	Deal3		Match3
5	0	Deal4		Match4
6	0	Deal5		Match5
7	1	Match1
8	2	Match2
9	3	Match3
10	4	Match4
11	5	Match5
12	5	Problem1
13	5	Problem2
14	5	Problem3
15	5	Problem4
16	5	Problem5
17	5	Condition1
18	5	Condition2
19	5	Condition3
20	5	Condition4
21	5	Condition5
Sheet1

 

[Domski]

Hi,

Vlookup is more used to return a value from an array of data based on criteria. It can be set to look for either exact matches or a match equal to or less than the criteria. It's not really intended for what you are suggesting.

One of the main problems you'll have is specifying what you mean by a 'best match'. Do you just mean where the first few characters match or are you meaning some kind of fuzzy logic that looks at patterns in what has been entered?

One way or the other you're looking at some quite complex formula or VBA to achieve what you require I think.

Dom

 

[OVERPACKED]

THANKS FOR RESPONDING

HI ALL,

my 1st post ever and i cant believe the responses came so quick - THANKS
I have pasted part of the table array below

site no address
001 anywherw
001 nowhere
002 etc
002 etc
ect up to 4500ish

i am not realy sure that i discribed the problem fully 1st time so i will try again.

The user inputs into a cell on another page the site number and the adress is auto filled into the next cell how do i get it to show a drop down menu containing the all the data that matches the users input so the user can then select the correct one?