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.
You can alter your current formula to use a VLOOKUP as follows:
=IF(ISNUMBER(MATCH(C1,A:A,0)),VLOOKUP(C1,A:B,2,0),"")
The above will give you the corresponding B value for the matching A number, and give you blank if there are no matches.
Best Answer
If I understand your question correctly, you would like a formula that will read the first and last name on F1, locate the corresponding row on F2, and return the email address. This array formula will accomplish this. Make sure you press Ctrl+Shift+Enter when entering the formula because it is an array formula.
This assumes the following:
Note that the row numbers should be changed depending on where you enter the formula (the
1's in "A1&B1".