Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    London, Gtr London, England
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Lookup - exact match of text (9.0.3821 SR1/Win95A)

    I have a two column lookup section on one part of the sheet in to which I enter file names and numbers - the aim is to match someone's last name with the file number. You enter Smith in the working part of the spreadsheet, it looks up to the 2 column section and matches "Smith" with its file ref "01/1234A". Trouble is, I keep getting new files and am not always sure if the last name for a new file is in the table. Right now, if it can't find Smith, it will find the last name nearest in spelling to Smith and give you the file ref for that file.

    How do you use Lookup, or Vlookup or Hlookup, so that it will only give a file ref if there is an exact match?

    Also, can you workaround having "#NA" showing in the file ref column where the cell you put the last name in to is blank? Aesthetically more pleasing just to have a blank cell, not "#NA".

    Regards
    Will Williams

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Lookup - exact match of text (9.0.3821 SR1/Win95A)

    Hi Will,
    There is a fourth argument for vlookup (and hlookup) which you set to False if you want an exact match. To get rid of the #N/A, you can use something like:
    =if(isna(vlookup(A1,FileList,2,false)),"",vlookup( A1,FileList,2,false))
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Star Lounger
    Join Date
    Jan 2002
    Location
    London, Gtr London, England
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup - exact match of text (9.0.3821 SR1/Win95A)

    You could also use
    b1=vlookup(a1,filelist,2,false)
    and then in the cell with your equation set the conditional formatting to :
    isformula =isna(b1) and set the font colour to white.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Lookup - exact match of text (9.0.3821 SR1/Win95A)

    Nice suggestion!
    There's also a generic error handling function posted in this forum somewhere (did a search and it's <!post=here,87625>here<!/post>) that a few Loungers put together for exactly this sort of situation - it will handle any error and return whatever message you like.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    London, Gtr London, England
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Lookup - exact match of text (9.0.3821 SR1/Win95A)

    Just to thank you for your suggestions. I've got it working better as a result!

  6. #6
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    London, Gtr London, England
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Lookup - follow up

    The workbook I am using is not one I devised. I can make entries on any of 6 pages, each of which page will need to vlookup the same range for the information. I have tried to add 'Page 1'! to the formulas on Pages 2 thru 6, and this works, except that it adds [page 1] to the formula before 'Page 1'! and then I have to tell it to find the information in the same workbook. When I open the workbook, it asks me if I want to link to it to update the information. Where I am going wrong?

    Will

Posting Permissions

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