use a VLOOKUP:
=VLOOKUP($A2,Sheet2!$A2:$B$240,2,FALSE)
Put this in every row on sheet 1 where there is a row of data, in a blank column next to the data. It would look at the ID in that row, look for that ID in sheet 2, then return the value it finds.
=VLOOKUP(AdjacentCellWithID,TargetTable,NumberOfColumnsAcrossFromLeft,FALSE)
I would also recommend you use tables, this way you can dynamically refer to the ranges, meaning less work in future to keep the function working:
=VLOOKUP([@[ID]],[ValuesTable],2,FALSE)
This should be useful: http://chandoo.org/wp/2012/03/30/comprehensive-guide-excel-vlookup/
And finally:
Looking at your last line, you want to find the difference between the two values?
So you could do this:
=[@[Value]-VLOOKUP([@[ID]],[ValuesTable],2,FALSE)
or
=$B2-VLOOKUP($A2,Sheet2!$A2:$B$240,2,FALSE)
Without knowing more about your data I can't be sure if the two values are the right way around.
Look into MATCH()
- it will display N/A
for not found, IF(ISNA(...),...)
will help for those if you wish to display something else. Example sheet below.
-- Untitled.csv --
Left,Sent+NR,Returned,"nonS,Returned"
a,"=MATCH(A2,$C$2:$C$8,0)",e,"=MATCH(C2,$A$2:$A$8,0)"
b,"=MATCH(A3,$C$2:$C$8,0)",g,"=MATCH(C3,$A$2:$A$8,0)"
c,"=MATCH(A4,$C$2:$C$8,0)",a,"=MATCH(C4,$A$2:$A$8,0)"
d,"=MATCH(A5,$C$2:$C$8,0)",b,"=MATCH(C5,$A$2:$A$8,0)"
e,"=MATCH(A6,$C$2:$C$8,0)",c,"=MATCH(C6,$A$2:$A$8,0)"
f,"=MATCH(A7,$C$2:$C$8,0)",x,"=MATCH(C7,$A$2:$A$8,0)"
g,"=MATCH(A8,$C$2:$C$8,0)",y,"=MATCH(C8,$A$2:$A$8,0)"
Best Answer
You are probably going to want some variant of VLOOKUP to get this done. The trick is that you want your VLOOKUP to return a true or false. My method for getting a true/false from VLOOKUP is this:
this returns true if it finds a value, and false if it doesn't. (if someone knows a better way to do this, I'd love to know it!)
So now you put one of those statements for each of your columns inside an AND statement & you should have your test!
That was kinda long, but I hope it helps!