+ Reply to Thread
Results 1 to 11 of 11

if vlookup yields no results then try another vlookup

  1. #1
    Registered User
    Join Date
    12-17-2013
    Location
    Greater NYC Area
    MS-Off Ver
    Excel 2010
    Posts
    84

    if vlookup yields no results then try another vlookup

    I need some help with some nested formulas. I've got too many nests and I'm mixing them up.

    So in the attached data sheet you'll see three chunks of data. I've simplified this dramatically from what I'm actually working with.

    Goal -
    If item from data set 1 is in either column 1 or 2 of "compared with this data set" then return "true" in to "data set evaluated" area
    if item from data set 1 is not in EITHER column 1 or column 2 of "compared with this data set" then return value "false" in to "data set evaluated" area
    additionally the tables I'm working in from data set 1 come in varying length in terms of number of rows. If there are fewer rows in data set 1 then I just want the cell to be blank (I don't a big "N/A" error there)

    I tried several different if(iserror(isblank(vlookup(vlookup)))) combinations - not in that specific order - to yield a result with no luck.

    Thanks in advance!
    Attached Files Attached Files

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: if vlookup yields no results then try another vlookup

    Try this in A13 filled right/down to B20

    =IF(A2="","",COUNTIF($I$3:$J$9,A2)>0)

  3. #3
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: if vlookup yields no results then try another vlookup

    FYI,
    Quote Originally Posted by IronCladRooster View Post
    I've simplified this dramatically from what I'm actually working with.
    That's usually not a good idea.
    Solutions to the 'simplified' version of a problem are often not applicable to the actual version.

  4. #4
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,213

    Re: if vlookup yields no results then try another vlookup

    Not sure I understand completely but...

    In A13:
    =IFERROR(not(ISERROR(VLOOKUP(I3,Table2[Data Set 1],1,0))),ISERROR(VLOOKUP(I3,Table2[Column1],1,0)))

    In B13
    =IFERROR(not(ISERROR(VLOOKUP(J3,Table2[Data Set 1],1,0))),ISERROR(VLOOKUP(J3,Table2[Column1],1,0)))



    ?
    You should hit F5, because chances are I've edited this post at least 5 times.
    Example of Array Formulas
    Quote Originally Posted by Jacc View Post
    Sorry, your description makes no sense. I just made some formula that looks interesting cause I had nothing else to do.
    Click the * below on any post that helped you.

  5. #5
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,556

    Re: if vlookup yields no results then try another vlookup

    Sorry, your description makes no sense. I just made some formula that looks interesting cause I had nothing else to do.

    If you explain your problem better I can solve it for you. Enter some expected output, that will make it easier to understand what you want.
    Attached Files Attached Files
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  6. #6
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,213

    Re: if vlookup yields no results then try another vlookup

    Quote Originally Posted by Jacc View Post
    Sorry, your description makes no sense. I just made some formula that looks interesting cause I had nothing else to do.
    I'm putting that in my sig.

  7. #7
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,556

    Re: if vlookup yields no results then try another vlookup


    ''''''''''''''

  8. #8
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,556

    Re: if vlookup yields no results then try another vlookup


    ''''''''''''''

  9. #9
    Registered User
    Join Date
    12-17-2013
    Location
    Greater NYC Area
    MS-Off Ver
    Excel 2010
    Posts
    84

    Re: if vlookup yields no results then try another vlookup

    I want to quote each of the posts in this thread because they're all gems.

    Thanks for your help. The formula results solved the problem.
    Sorry for not being very clear with my question in the first place.
    Thanks for the laugh Jacc!

  10. #10
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: if vlookup yields no results then try another vlookup

    You're welcome.

  11. #11
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,556

    Re: if vlookup yields no results then try another vlookup

    So what was the solution?
    Edit: Or rather which formula solved your problem?

+ 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. [SOLVED] Using VLookup but results show formula instead of results...
    By excel me in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 11-28-2013, 09:40 PM
  2. VLookup that Results with False or Blank Results
    By mycon73 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-12-2013, 07:16 PM
  3. Worksheet function Vlookup and VLOOKUP return different results
    By zandero in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-08-2010, 08:24 AM
  4. Custom Autofilter yields no results
    By rjejyork in forum Excel General
    Replies: 4
    Last Post: 07-20-2006, 04:15 PM
  5. [SOLVED] 6th order polynomial equ yields unexpected results
    By Sanjay Kumar Limbikai in forum Excel Charting & Pivots
    Replies: 11
    Last Post: 09-26-2005, 08:05 PM

Tags for this Thread

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