VLookup Assistance

[berger01]

I have a Vlookup formula that is not returning what I expect. Can someone take a look?

I have 4 sheets. Each sheet looks back at the previous (column D) to see if the same record exists, and if it does then returns the value from column "Q" and then adds 1 to that number. All ranges are labled and the formula works but not accurately. The first worksheet, the user enters a number manually and the next three calculate on their own. The main problem is that when records match from both worksheets the second worksheet should capture the value in Column Q and return it + 1 into Column Q of the second.... But it doen't.

Attached is the file.

Thanks to anyone who can offer a solution.

[NBVC]

Can you specifically pinpoint a cell that is giving the wrong result and tell us why it is wrong or what you expect instead?

[berger01]

Hi there NBVC.
Please notice that all records in the 4 sheets are the same. This is to show the issues. If you look at the 1st sheet column Q, the 1st and 2nd record: There is the number 1 in that cell. If you look at the same in the 2nd sheet, you will see blanks. The VLookup should be returning the number 2 because it recognizes the same record, it should return that value (column Q)from the 1st sheet and add 1.

Hope this helps, and thanks for looking.

[NBVC]

In this part:

IF(OR(ISNA(VLOOKUP(D2,QTR1_2011,14,FALSE)),VLOOKUP(D2,QTR1_2011,14,FALSE)=1),"",....

you test whether the result of the Vlookup is #N/A or if the result is 1.. and if either is TRUE, you say, return a null ("") so in those cases the result of the Vlookup is 1, and hence the blanks instead of 2....

[berger01]

I see that. If I keep the N/A and change the "1" to a 0, then it will not add the +1 to 0 on the second sheet and return a 1. If I change the "1" to a Null "", then it can't add the +1 to a null.

How can I ask for a blank field in sheet#1 to return a "1" in sheet two only based on the fact that the record was found in sheet 1? thats the goal.

[NBVC]

How about removing that part of the formula altogether:

e.g

=IFERROR(IF(OR(P2=1,P2=2,P2=3),IF(ISNA(VLOOKUP(D2,QTR1_2011,14,FALSE)),"",(VLOOKUP(D2,QTR1_2011,14, FALSE)+1) ),""),"")

[berger01]

Your the best: "Where there is a will there are many ways" Thanks NBVC