VLOOKUP/Search issue

**dabman** · 12-05-2008, 07:29 AM

Hey folks,

I currently have a sheet contains 2 columns, one of which contains a product code (either 10 or 13 digits long) and the other which contains the name of product.

(so for arguments sake, A1 contains product code, and B2, the product name)

I am trying to create a very quick reference tool which allows the user to enter a prospective product code or product name and have the full details returned, confirming its existence in the list (and possibly some other data too).

I've tried this with VLOOKUP but either have issues with the wrong titles being returned, or an issue in converting the product code column of the array, from custom format to text (as I believe I need to do this for a result to be returned).

Can anyone offer some advice on sorting this?

**arthurbr** · 12-05-2008, 07:35 AM

Maybe you can start with dependent lists

**martindwilson** · 12-05-2008, 07:36 AM

please post a sample indicating which are right/wrong, but dont forget
1.vlookup needs the qualifier FALSE to find exact match
2. it only works to right ie your look up value must be in the first column of your table ,in your case col a
and i presume the other is in col b not a2 as you stated.
there are work arounds to look to the left, either copy a to col c and use vlookup with isna/if or use index match which can retrieve data in any direction.

**dabman** · 12-05-2008, 09:29 AM

Hi,

Thanks for the replies. I've created a basic mockup of what I am looking for, removing any pertinent data of course.

Martin, to address your points specifically:

1. Yup, and thats still an option. However I decided to omit that since the user may not know exactly how the name of the product is represented in the list, and hopefully that omission will give them a decent chance of pulling up the item they are looking for.

2&3. Yup, this much I knew (though I appreciate that my OP may not have reflected that

). I've built my sheet accordingly but am still experiencing problems.

Hopefully you can point me in the right direction.

Thanks in advance for your efforts.

B

P.S. Part of my issue is that, even if I can get this working, I need to find a way to effectively convert the code field in the list from custom to text (as I believe this is necessary for the VLOOKUP to work correctly). I get this data on a daily basis from an external source who sets the field as a customer much like in the mockup. Any advice on this would also be appreciated.

**mewingkitty** · 12-05-2008, 10:03 AM

This is how I'd do it.

Columns in the wrong order? Make a page that switches them.

There's other ways to go about it, but I kinda like the simple ones.

Works if formatted as text or if general, tried them both.

**martindwilson** · 12-05-2008, 10:21 AM

try this see spreadsheet

**dabman** · 12-05-2008, 10:40 AM

Thanks to both of you.

A couple of questions if I may.

Martin - Looking at your sheet (and kitty's for that matter), you seem to have switched the data around so that the prod code comes second rather than first. Is there some significance to the order the data is in, or am I missing something here.

To my eyes, it seems that the changes you made were to change the columns of list data around, and then add the FALSE command to the end of the VLOOKUP? Does that sound right?

If so, how can I get this to function as a closest matching search (i.e. without the FALSE). My interpretation of the VLOOKUP function is that omitting the FALSE (or putting TRUE in there) should accomplish this, but it didn't seem to work as I had expected it to.

Thanks again for your efforts folks.

B

**martindwilson** · 12-05-2008, 11:07 AM

some things you didnt notice!
product search
=IF(ISERROR(VLOOKUP(--$B$7,'Data List'!$A$1:$B$6,1,FALSE))," ",VLOOKUP(--$B$7,'Data List'!$A$1:$B$6,1,FALSE))
the double -- converts the text number to a real number to look for on your data sheet
as i said you can only look to the right so simplest way is to duplicate the first column and put it to the right however
you can use index match as in this version so no need of duplicating columns,
as for closest match only you can decide what you want, and it depends on how things are sorted.
you can wrap this up in if/isna if you want

**mewingkitty** · 12-05-2008, 12:40 PM

The significance of the order of the columns lies in the way VLOOKUP functions. It looks at the left-most column of the cells you choose, and returns the corresponding cell in the... here... check out the attached sheet, it's much easier to explain with an example.

mew!