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.
Best Answer
VLOOKUP
is what you are looking for.In column Y2:
=VLOOKUP(X2;A1:B3;2;FALSE)
Using
VLOOKUP
you can find values in a table or matrix. Using it is quite simple:=VLOOKUP(*lookupvalue*;*MATRIX*;*columnindex*;*rangelookup*)
The columnindex is based on your matrix.
Example: If your matrix is G1:J20, G* would have the columnindex 1, H* 2, I* 3, J* 4.
Range Lookup: TRUE assumes the first column in the table is sorted either numerically or alphabetically, and will then search for the closest value. This is the default method if you don't specify one.
FALSE is looking for the exact match.
More information can be found here
I've created you an example. Please note that I'm using a german excel client - therefore my formula isn't
VLOOKUP
, it is "sverweis"...https://drive.google.com/open?id=0BxW3WdSITmbjTEw1bm1Gb2l1eWc