Help with Vlookup and IF statement please

**gvishnu** · 01-23-2013, 10:59 AM

Hello,

I am trying to get the values from tab 2 of the same document into tab 1 of the said document. I then want to use the if statement to do the following:

IF value is < 2012 then Existing - which is happening but here is where I run into issues,

If value from the vlookup is #N/A then New.

This is the formula that I am using.

=CLEAN(IF(VLOOKUP(A2,'All 2012 TR GLs Repeat History'!A:J,4,FALSE)<2012,"Existing",VLOOKUP('All 2012 TR GLs'!A2,'All 2012 TR GLs Repeat History'!A:J,4,FALSE)))

The issue is that the second tab does not contain all the records that are there in the first tab.

Any help is appreciated, Thank you

**Pauleyb** · 01-23-2013, 11:47 AM

Not entirely sure how your formula ties into the data, but based upon your description, this could work, or at least lead you down the path:
=IF(ISNA(VLOOKUP(A2,'All 2012 TR GLs Repeat History'!A:J,4,FALSE)<2012,"New","Existing")

**gvishnu** · 01-23-2013, 12:06 PM

Hi Pauley...thanks for helping out..Unfortunately the above formula is still throwing error...I get "too many arguments entered even after fixing the parenthesis. Will a copy of the document help you?

**Pauleyb** · 01-23-2013, 05:22 PM

Sorry, should be:
=IF(ISNA(VLOOKUP(A2,'All 2012 TR GLs Repeat History'!A:J,4,FALSE)),"New","Existing")

This basically returns New if VLOOKUP cannot find the match, and Existing if it can. I took out the <2012, since it is not needed in the example I provided.

What is unclear from your first post is that you say

If value from the vlookup is #N/A then New

Which VLOOKUP? You have two in your offered formula. Also, your formula does not have the text "New" in it.

In trying to read between the lines, it appears to me that you did not know how to handle the case of VLOOKUP returning the #N/A error. I hope my example, while not being the final solution, shows you how to check for the error and resolve it.

**gvishnu** · 01-25-2013, 12:54 PM

Your solution worked perfectly for me. Thank you very much. You are right when you say that I did not know how to handle the vlookup returning the #N/A error. I have now learned how to fix it in the future.