Formula to match a data point in 2 wrkshts
 

I need to match a data point in a new report with the previous report and then pull in a note for that matching data point.

Each month I get a report have look at the data then make a note:
Column A:work number Column D:note made by me

The data changes some of the data in A: drops off which then would make note in column D: unimportant for the current report.

I played around with various versions of:
=if(A1<””,A1,””) - this was no where near complex enough to grab data from another worksheet with data points that drop off the list.

then tried this one:
=IF(('May 2012 Report'!A3:A226='June 2012 Report'!A3),'May 2012 Report'!D3: D226<"",'May 2012 Report'!$D$2:$D$225) (put space between : and D got a grin)

This one gives me a True/False statement in the first to cells then it keeps referring to empty cells.

Does not account for the change in my data in Column A: so records drop everything in D: even if A: in worksheet 1 does not correspond with data in worksheet 2.

Any suggestions to fix this

Hi,
I can see why your formula is not working, but a dummy version of the workbook will be needed in order to write the correct formula for you.

If you could post one i'll be happy to help.

Feel free to PM me for an email address if your data cannot be posted here.

Spencer.

see a dummy work up attached

Try the below formula in cell D2 on the June 2012 Report tab and copy down.
(Adjust the 21 near the end of the formula to the relevant last row on the real workbook)

=VLOOKUP($A2,'May 2012 Report'!$A$2:$D$21,4,FALSE)

It will feed in the "Note" from the previous report, if applicable, give #N/A if the reference in column A does not appear on the previous report and give a zero if the reference number appears but there was no note recorded.

Does that do what you're after?

Let me know either way and I will either adjust the formula if it's not what you meant, or sit back and enjoy being of help if it is :)

S.

That one works gives me what I am looking for but one thing:
1. several cells have #N/A

Tried <"" at the end but that just screwed up the formula giving me a false answer. Is there away to get a cell that does not match to be either blank or have a 0?

On the proviso you're using Excel 2007 or later, you can use...


=IFERROR(VLOOKUP($A2,'May 2012 Report'!$A$2:$D$21,4,FALSE),"")

This works great. Thanks

Not a problem. Anytime! :)