Can anyone tell me if there is a formula in Excel to do the following:-
I have two columns of numbers: I want to check if the numbers on the left column occurs in the second column and to return a positive or negative answer? It will have to take the first number in the first Column and check all the numbers in the second column and so on ???
If you want to know where in colum2 your value in colum 1 is then you can use the match function:
=MATCH(lookup value,look up array,look up type)
for example =MATCH(A2,B$2:B$11,0) this matches A2 in colum A against all the values between B2 to B11. 0 defines that it must be an exact match. You can then drag this formula all the way down next to the look up values and it will return a number which corresponds to the line number where it matches a number in the column against which your are checking. Don't forget to add the $ in your formula to lock the rows, otherwise when you drag down the formula the array changes.
A B Row Number B is A in B? 1 1 Row1 1 3 2 Row2 3 5 3 Row3 5 7 4 Row4 7 9 5 Row5 9 2 6 Row6 2 6 7 Row7 6 4 8 Row8 4 8 9 Row9 8 0 10 Row10 #N/A