Solved IF statements with nested Vlookups

May 13, 2016 at 12:29:22
Specs: Windows 7
I am trying to compare the contract ID in one spreadsheet to the contract ID in another spreadsheet and if they match bring back the data from the comments column on the second spreadsheet into the first spreadsheet. I am getting #N/A

=IF(VLOOKUP('F:\Management Information\2016\UW MI Only\04 April\Month End\MI Compliance\[Data Quality Report- As at April 22, 2016.xls]Summary'!D2,$A$2:$D$246,4,TRUE)>=D2,"'F:\Management Information\2016\UW MI Only\04 April\Month End\MI Compliance\[Data Quality Report- As at April 22, 2016.xls]Summary'!$A$2,$Q$2:Q246,17,FALSE","BLANK")


See More: IF statements with nested Vlookups

Report •


✔ Best Answer
May 16, 2016 at 17:21:33
The basic VLOOKUPs should look like this:

To return the value from Column P:

=VLOOKUP(D2,Rpt_2!$D$2:$Q$246,13,0)

To return the value from Column Q:

=VLOOKUP(D2,Rpt_2!$D$2:$Q$246,14,0)

Both of the formulas above will return #N/A if the lookup_value is not found. Assuming you are using Excel 2007 or later, the formulas below will return a blank cell if the lookup_value is not found.

To return the value from Column P:

=IFERROR(VLOOKUP(D2,Rpt_2!$D$2:$Q$246,13,0),"")

To return the value from Column Q:

=IFERROR(VLOOKUP(D2,Rpt_2!$D$2:$Q$246,14,0),"")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
May 13, 2016 at 12:55:05
Obviously, I can't test your exact formula without creating that long path, the workbooks, the sheets, etc, so the best I can do is visually see if I can find the problem.

I'm confused by a bunch of things:

1 - The range_lookup argument for your VLOOKUP is set to TRUE. Does that mean that you are not looking for an exact match?

2 - You have quotation marks around the value_if_true argument of the IF:

"'F:\Management Information\2016\UW MI Only\04 April\Month End\MI
Compliance\[Data Quality Report- As at April 22,
2016.xls]Summary'!$A$2,$Q$2:Q246,17,FALSE"

Quotation marks means that you want to return that as a text string. Is that what you want to do?

3 - If not...Is that supposed to be a second VLOOKUP? If so, then where is the actual function name: VLOOKUP for that section?

4 - If that is supposed to be a VLOOKUP, then you are asking the VLOOKUP to return a value from the 17th column of the a single column table_array: Q2:Q246. Even if you add the VLOOKUP function, that still won't work.

There seems to be a number of things wrong with the construction of your formula.

Please clarify...

message edited by DerbyDad03


Report •

#2
May 16, 2016 at 06:45:36
Many thanks DerbyDad03!

1 - I am trying to look for an exact match on reference ID from one XL report to another.
2 - If the reference matches on both reports, I want to bring back the data from report 2 column Q to report 1 column Q.
3 - I guess this must be a second vlookup to bring the info in Q back
4 - Yes, I am trying to bring the data back from column Q

Many thanks,


Report •

#3
May 16, 2016 at 09:31:20
I have to admit, I'm lost. I can't tell what sheet contains the value to look up, where the lookup table resides, etc.

Let's make this simpler for both of us. To eliminate that long path string, please refer to your 2 reports as Rpt_1 and Rpt_2. You can substitute the real path back in on your end once we figure this out.

1 - In which report do you want to put this formula?
2 - Which report contains the list of ID's that need to be searched for?
3 - Which column in that report contains the list?
4 - Which report contains the column of ID's that need to be searched.
5 - Which column in that report contains the list of IDs to be searched?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Related Solutions

#4
May 16, 2016 at 10:03:23
Hi DerbyDad03,

Thanks again for helping me out. Can I email you the reports? May be that would make things easier and I can state step by step what it is I am trying to do?

Email xxx@xxx.xxx with your email and I will send the reports.
Thanks again,

edited by moderator: Removed email address


Report •

#5
May 16, 2016 at 10:17:09
Please do not post plain text email addresses in this forum. It's not that we really care if you get spammed or not, but we don't want this forum to become known as a place where email addresses can be harvested. Once the bad guys start hanging around, they are very hard to get rid of.

I have edited your post to remove your email address.

Please post the files at a file sharing site such as zippyshare.com. Please remove any confidential or personal information. Please do not use a file sharing site that requires us to register, because odds are that we will not.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
May 16, 2016 at 10:27:01
My apologies...I left this out:

After you post the files to a file sharing site, post a link to the files back here in the forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
May 16, 2016 at 12:11:13
Maybe this is all you need.

Put this in Column Q of Rpt_1:

=IF(ISNA(VLOOKUP(A2,Rpt_2!$A$2:$Q$246,1,0)),"",
VLOOKUP(A2,Rpt_2!$A$2:$Q$246,17,0))

It will search for the value in Rpt_1!A2 in Rpt_2!$A2$:$A$246. If it doesn't find it, it will return a blank cell. If it finds it, it will return the value from the corresponding row in Rpt_2!Column Q.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#8
May 16, 2016 at 12:49:38
Thanks for your help! I will give that a try. Unfortunately, I am unable to post these reports on any shared website. It sounds like the formula you gave me above is what I may be looking for.

I want to compare column D (policy) in report 1 to column D (policy) in report 2 and if the policy number matches then pull the data in column P and Q from report 2 into report 1. Does that make sense? I haven't worked with vlookups before and I am finding it challenging putting this formula together.

Thanks for all your help


Report •

#9
May 16, 2016 at 17:21:33
✔ Best Answer
The basic VLOOKUPs should look like this:

To return the value from Column P:

=VLOOKUP(D2,Rpt_2!$D$2:$Q$246,13,0)

To return the value from Column Q:

=VLOOKUP(D2,Rpt_2!$D$2:$Q$246,14,0)

Both of the formulas above will return #N/A if the lookup_value is not found. Assuming you are using Excel 2007 or later, the formulas below will return a blank cell if the lookup_value is not found.

To return the value from Column P:

=IFERROR(VLOOKUP(D2,Rpt_2!$D$2:$Q$246,13,0),"")

To return the value from Column Q:

=IFERROR(VLOOKUP(D2,Rpt_2!$D$2:$Q$246,14,0),"")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
May 23, 2016 at 04:55:09
Hi DebyDad03,

Many thanks for all your help! I have know got the formula I was looking for.

Thanks again!


Report •

Ask Question