Excel Formula Query

C

careywall

Registered User
Messages
10
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 ???

careywall
 
if i understand your problem correctly Vlookup should do the trick
 
This should do the trick, dont know why it wont bring back false instead of N/A

all data in column
A
B

Query written from column c
=IF(VLOOKUP(A1,$B$1:$B$8,1,0),TRUE,FALSE)

A B C

1 1 TRUE
3 7 TRUE
4 5 TRUE
5 3 TRUE
6 87 #N/A
7 3 TRUE
8 7 #N/A
3 4 TRUE
 
countif($b1:$b8,a1)

this returns the number of occurences of the a1 value within the range b1:b8

if your formula is in c1

then

if(c1>0,"true","false")
 
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
 
Thanks everyone for your replies have been away for a few days but am looking forward to trying them out over the weekend

careywall
 
You must log in or register to reply here.
Back
Top