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

1. ## 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:
`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...

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

Originally Posted by roscoepwavetrain

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)

3. ## 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,"")

4. ## 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.

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. ## 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. ## Re: Help with multiple vlookup with if, and(isna())

Originally Posted by Tony Valko
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!

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

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

TestDataSet.xlsx

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

sorry, duplicate post. see below!

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

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. ## Re: Help with multiple vlookup with if, and(isna())

those are the names of your tab

so

Summer2013Data
Spring2013data
fall2012 data

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. ## 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. ## 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. ## Re: Help with multiple vlookup with if, and(isna())

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. ## Re: Help with multiple vlookup with if, and(isna())

regards,

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

#### 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