+ Reply to Thread
Results 1 to 14 of 14

Help with multiple vlookup with if, and(isna())

  1. #1
    Registered User
    Join Date
    05-13-2013
    Location
    Denver
    MS-Off Ver
    Excel 2010
    Posts
    7

    Help with multiple vlookup with if, and(isna())

    I'm trying to create a formula that will look for a binary yes/no across multiple data collection events, each in its own sheet, with each record identified by a student ID.

    The structure I have is: Sheet 1, list of student IDs, location of Vlookup statement. The other sheets, two of them for now, are a list of student IDs in the rows with a column of binary yes/no (0/1) data. These sheets of data are defined as range1 and range2. An important characteristic of my formula is that it must honor the fact that the student ID lists between all of the sheets may be different due to population mobility.

    What I'd like my formula to do is vlookup the student ID. If there's an ID match and any of the ranges that match have a 1 in the binary column, then the master sheet would report a 1. If there's a match and all of the ranges that match have a 0, then the master sheet would report a 0. If there's no ID match to any of the sheets, then there would simply be a blank on the master.

    The formula below is the closest I've come. It works in every scenario except when there's a match in range1 but not in range2 or vice versa. The problem is that the vlookup nested in the sum() in the second half of the formula returns an error, causing the entire formula to report an error.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Basically, my question boils down to this: how do I contend with a sum(vlookup(1),vlookup(2)), if one of the vlookups might possibly return an N/A?

    Thanks and sorry for the text wall...
    Last edited by roscoepwavetrain; 10-16-2013 at 09:20 PM. Reason: Oops...typo!

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Help with multiple vlookup with if, and(isna())

    Quote Originally Posted by roscoepwavetrain View Post

    Basically, my question boils down to this: how do I contend with a sum(vlookup(1),vlookup(2)), if one of the vlookups might possibly return an N/A?
    Something like this:

    =IFERROR(VLOOKUP(1),0)+IFERROR(VLOOKUP(2),0)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2010
    Posts
    4,332

    Re: Help with multiple vlookup with if, and(isna())

    another think... this doesn't make sense.

    IF(SUM(VLOOKUP(A1,range1,2,FALSE),VLOOKUP(A1,range2,2,FALSE))>0,1,1))
    the 1,1 at the end

    you can eliminate the if isna at the beginning... then to the shorter one with Biffs iferror to this one if I my though is right

    =if((iferror(VLOOKUP(A1,range1,2,FALSE),0)+iferror(VLOOKUP(A1,range2,2,FALSE),0))>0,1,"")
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  4. #4
    Registered User
    Join Date
    05-13-2013
    Location
    Denver
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Help with multiple vlookup with if, and(isna())

    Thanks for the suggestions. Both of those are close but don't quite meet all of my hopes. Basically, I was hoping for one of three results:

    1. The vlookup finds one or more 1s, in which case it reports a 1.
    2. The vlookup finds at least one 0 but no 1s, in which case it reports a 0.
    3. The vlookup errors out on all lookups, in which case the cell is blank.

    I've been able to write a couple of different formulas that can find satisfy two of the above at the same time, but not all three.


    Quote Originally Posted by vlady View Post
    another think... this doesn't make sense. the 1,1 at the end
    Thanks, that was an error in my typing the formula. I edited the OP to correct it.

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Help with multiple vlookup with if, and(isna())

    Can you post a SMALL sample file so we can see what you're trying to do in context?

    SMALL = no more than 20 rows worth of data.

    Make sure you tell/show us what result(s) you expect.

  6. #6
    Registered User
    Join Date
    05-13-2013
    Location
    Denver
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Help with multiple vlookup with if, and(isna())

    Quote Originally Posted by Tony Valko View Post
    Can you post a SMALL sample file so we can see what you're trying to do in context?

    SMALL = no more than 20 rows worth of data.

    Make sure you tell/show us what result(s) you expect.
    Attached. The first sheet is what I'll consider my master-where my current students' IDs will be pasted and where I'll want the formula to populate results. I left column B there for folks to test their formulas, and manually entered my expected Column B results in Column C. I also did vlookups on the three named ranges (range1, range2, and range3) in Columns D,E, and F so folks would be able to see the independent data from the other sheets quickly.

    What I want is to poll previous data collection sheets to see if students have ever been flagged as a previous dropout (i.e. have a 1 in the previous collection, even if there are 0s, too). If they were ever flagged, I want them flagged as a 1 in the master sheet, no matter when/how many times they've been interviewed.

    If they've ever had data collected before but weren't a previous dropout (i.e. have a 0 in the previous collection but no 1s), they should have a 0 in the master.

    If they're in the master but no other sheet, I'd like a blank cell in the master.

    Let me know how I can help more. I really appreciate your quick responses!
    Attached Files Attached Files

  7. #7
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2010
    Posts
    4,332

    Re: Help with multiple vlookup with if, and(isna())

    maybe this one ...Not really tested on other instances.

    TestDataSet.xlsx

  8. #8
    Registered User
    Join Date
    05-13-2013
    Location
    Denver
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Help with multiple vlookup with if, and(isna())

    sorry, duplicate post. see below!

  9. #9
    Registered User
    Join Date
    05-13-2013
    Location
    Denver
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Help with multiple vlookup with if, and(isna())

    Quote Originally Posted by vlady View Post
    maybe this one ...Not really tested on other instances.
    Well, that definitely works with the dataset I included. Now I have to figure out how to actually use it (aka what the hell is going on in there )so that I can scale it to my needs!

    One place to start...on the sheet, you added "Named Range Lists", which the named range lists points to those cells. However, I can't find where those text names refer to actual ranges...

  10. #10
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2010
    Posts
    4,332

    Re: Help with multiple vlookup with if, and(isna())

    those are the names of your tab

    so

    Summer2013Data
    Spring2013data
    fall2012 data

    are your tabs


    using sumproduct and indirect to that list:... first part

    =IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&lists&"'!A2:A15"),A2))=0,"", --> this is use to look for the current student ID from the 3 sheets you have (named range "LISTS")

    if it cannot find the ID on those 3 sheets (LISTS) then return "" null otherwise go to the next if

    ,IF(SUMPRODUCT(COUNTIFS(INDIRECT("'"&lists&"'!A2:A15"),A2,INDIRECT("'"&lists&"'!B2:B15"),1))>0,1,0)) -> go to the tabs then count how many 1's are there if there is >0 then put 1 otherwise return 0

  11. #11
    Registered User
    Join Date
    05-13-2013
    Location
    Denver
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Help with multiple vlookup with if, and(isna())

    Absolutely brilliant. So if I have, say, 6 tabs, and put the names of those tabs in the range "Lists", it'll look for the ID in the row the formula is in (in your example, A2), looking in the cell range A2:A15 in the now-identified 6 sheets?

    Couple more questions:
    1. If I have 100 IDs in the non-master sheets, do I just change ("'"&lists&"'!A2:A15") to read ("'"&lists&"'!A2:A101")? What about just making it ("'"&lists&"'!A:A")?
    2. If I add a, say, "Previous Behavior issues" column to my master and want to reference a "Previous Behavior Issues" in Column C in the reference sheets, do I just change the formula to look like this: =IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&lists&"'!A2:A15"),A2))=0,"",IF(SUMPRODUCT(COUNTIFS(INDIRECT("'"&lists&"'!A2:A15"),A2,INDIRECT("'"&lists&"'!C2:C15"),1))>0,1,0))? I tried it in the test spreadsheet and the results looked correct, but I'd rather understand that what I'm doing is correct rather than just trial and error my way through it.

    Thank you SO much for your help. I'm learning a TON tonight and if I get this formula working, I stand to save myself hours every quarter. Better yet, I can keep a master Excel file with all historical data in it...and not use a single vlookup which would definitely be a first for me.

  12. #12
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2010
    Posts
    4,332

    Re: Help with multiple vlookup with if, and(isna())

    Your explanation is better than mine

    yup that's the whole thing.
    change the range A2: A101 or up to what range you would like to accommodate it, and yes just add the sheet names in the list just be sure to adjust the range of the Named Range, in my sample the range is only from F15 to F17.

    the A:A would work but i prefer the first one using specific number of rows and on columns also.
    try also to read some of the solutions from the file below

    TestDataSet.xlsm

  13. #13
    Registered User
    Join Date
    05-13-2013
    Location
    Denver
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Help with multiple vlookup with if, and(isna())

    Quote Originally Posted by vlady View Post
    try also to read some of the solutions from the file below
    Thanks for that extra information. I'll definitely incorporate that.

    Thanks again for taking the time to help me. Your solution fit my needs exactly and I really appreciate it.

    Cheers and have a great evening!

  14. #14
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2010
    Posts
    4,332

    Re: Help with multiple vlookup with if, and(isna())

    Your welcome anytime.

    regards,
    Vladimir

+ 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. Excel 2007 : Summing multiple IF(ISNA(Vlookup))
    By jseufert in forum Excel General
    Replies: 1
    Last Post: 09-07-2011, 12:42 PM
  2. Excel 2007 : Isna/vlookup
    By owensjb2 in forum Excel General
    Replies: 4
    Last Post: 05-11-2010, 05:45 PM
  3. Vlookup using ISNA ??
    By Carl1966 in forum Excel General
    Replies: 3
    Last Post: 01-27-2009, 12:13 PM
  4. ISNA and vlookup
    By jbwizoz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-21-2008, 05:16 PM
  5. if isna and vlookup together
    By tina in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-20-2005, 10:06 AM

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