HELP WITH VLOOKUP

OVERPACKED

New Member
Joined
Jul 30, 2007
Messages
2
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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
ABCDE
1mat
20Deal1Match1
30Deal2Match2
40Deal3Match3
50Deal4Match4
60Deal5Match5
71Match1 
82Match2 
93Match3 
104Match4 
115Match5 
125Problem1 
135Problem2 
145Problem3 
155Problem4 
165Problem5 
175Condition1 
185Condition2 
195Condition3 
205Condition4 
215Condition5 
Sheet1
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
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

New Member
Joined
Jul 30, 2007
Messages
2
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?
 

Forum statistics

Threads
1,181,606
Messages
5,930,866
Members
436,764
Latest member
avalladarez

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top