+ Reply to Thread
Results 1 to 10 of 10

VLOOKUP/Search issue

  1. #1
    Registered User
    Join Date
    05-31-2007
    Posts
    11

    VLOOKUP/Search issue

    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?
    Last edited by dabman; 12-05-2008 at 07:48 AM.

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326
    Maybe you can start with dependent lists

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    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.

  4. #4
    Registered User
    Join Date
    05-31-2007
    Posts
    11
    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.
    Last edited by dabman; 12-08-2008 at 12:56 PM.

  5. #5
    Forum Contributor mewingkitty's Avatar
    Join Date
    09-29-2008
    Location
    Fort McMurray, Alberta, Canada
    MS-Off Ver
    Excel 2003
    Posts
    949

    Hm,

    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.
    Last edited by mewingkitty; 12-15-2008 at 01:46 PM.

  6. #6
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    try this see spreadsheet
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    05-31-2007
    Posts
    11
    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

  8. #8
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    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
    Attached Files Attached Files
    Last edited by martindwilson; 12-05-2008 at 11:10 AM.

  9. #9
    Forum Contributor mewingkitty's Avatar
    Join Date
    09-29-2008
    Location
    Fort McMurray, Alberta, Canada
    MS-Off Ver
    Excel 2003
    Posts
    949

    Vlookup

    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!
    Last edited by mewingkitty; 12-15-2008 at 01:46 PM.

  10. #10
    Registered User
    Join Date
    05-31-2007
    Posts
    11
    I've replaced this post as I think I was being short sighted and so have sorted the issue.

    Many thanks for the suggestions.

    B
    Last edited by dabman; 12-09-2008 at 12:49 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1