+ Reply to Thread
Results 1 to 6 of 6

Return cell if vlookup doesn't find match

  1. #1
    Registered User
    Join Date
    11-06-2018
    Location
    Springfield, Missouri
    MS-Off Ver
    2016
    Posts
    17

    Return cell if vlookup doesn't find match

    In the attachment you'll see a small example of what I'm looking at. In Column F I have =VLOOKUP(E2,C$2:D$1988,2,0) and this does exactly what I want. When ever it returns #N/A it means it didn't find a name match, but this means there is a position there instead, and I'd like that position returned into Column G.

    I've fumbled for a bit but I can't get any one formula to work correctly. If something can be added to the first =VLOOKUP formula, great. If it has to be its own formula in Column G, that works too. Either option is fine since I'm just copying the data to another document as text only.
    Attached Files Attached Files
    Last edited by mhkeim; 02-06-2019 at 12:20 PM.

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,178

    Re: Return cell if vlookup doesn't find match

    Instead of a picture...

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    HTH
    Regards, Jeff

  3. #3
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: Return cell if vlookup doesn't find match

    Try this:

    =IFERROR(VLOOKUP(E2,C$2:D$1988,2,0),E2)
    1. Click on the * Add Reputation if you think this helped you
    2. Mark your thread as SOLVED when question is resolved

    Modytrane

  4. #4
    Registered User
    Join Date
    11-06-2018
    Location
    Springfield, Missouri
    MS-Off Ver
    2016
    Posts
    17

    Re: Return cell if vlookup doesn't find match

    Updated. Sorry for that.

  5. #5
    Registered User
    Join Date
    11-06-2018
    Location
    Springfield, Missouri
    MS-Off Ver
    2016
    Posts
    17

    Re: Return cell if vlookup doesn't find match

    I tried this one initially. I'm trying to get it so that any Error it finds returns the position found in Column E and put it into Column G. I've added an example spreadsheet with how it looks works now and another sheet with how I'd like the end result to look like.

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,178

    Re: Return cell if vlookup doesn't find match

    Modytrane's formula in post #3 does exactly what you ask.

    Place the formula in F2 and copy down.

    If you want to use column G >> =IF(ISNA(F2),E2,"")

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 0
    Last Post: 05-22-2013, 04:40 AM
  2. Find macro that doesn't match entire cell contents
    By ScabbyDog in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-25-2013, 05:49 AM
  3. [SOLVED] Find match, find related cell and return that number
    By HelpHelpHelp in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-17-2012, 06:46 AM
  4. [SOLVED] VLOOKUP match multiple values to return one value + find min price
    By asgb in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-13-2012, 12:02 AM
  5. Return value and match cell colour from vlookup
    By G-Force61 in forum Excel General
    Replies: 1
    Last Post: 07-04-2012, 02:55 AM
  6. [SOLVED] Return alternate value if VLookup can't find match
    By SueJB in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-05-2006, 05:35 AM
  7. Replies: 1
    Last Post: 05-04-2005, 02:15 PM

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