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")

✔ 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_valueis not found. Assuming you are using Excel 2007 or later, the formulas below will return a blank cell if thelookup_valueis 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.

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_lookupargument 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_trueargument 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:

VLOOKUPfor 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

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 QMany thanks,

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_

1and 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 searchedfor?

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

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

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.

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.

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

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

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_valueis not found. Assuming you are using Excel 2007 or later, the formulas below will return a blank cell if thelookup_valueis 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.

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

Thanks again!

Ask Your Question

Weekly Poll