Formula to see if a number is allocated
 

I need to find out if a number is allocated or free. Basically on sheet1 is
a load of information. in Column G on this sheet is a unique identifier
number.

In sheet2 I have a list of numbers say from 1-100 which correspond to the
identifier numbers in sheet1. Id like a forumla something like:

"If A1 in sheet 2 = any number in column G in sheet1, then print Allocated,
else print free"

At the moment I have: =IF(A1=Sheet1!G:G,"allocated", "free")
But it does not work. What it is doing is If A1 = everything in column G
then say allocated, else say free. So the results im getting are all free.

Thanks in advance, any help is much appreciated

Formula to see if a number is allocated
 

Try

=ISNA(MATCH(A1,Sheet1!G:G,0))

HTH. Best wishes Harald

"Ernest Lai" skrev i melding
...
I need to find out if a number is allocated or free. Basically on sheet1
is
a load of information. in Column G on this sheet is a unique identifier
number.

In sheet2 I have a list of numbers say from 1-100 which correspond to the
identifier numbers in sheet1. Id like a forumla something like:

"If A1 in sheet 2 = any number in column G in sheet1, then print
Allocated,
else print free"

At the moment I have: =IF(A1=Sheet1!G:G,"allocated", "free")
But it does not work. What it is doing is If A1 = everything in column G
then say allocated, else say free. So the results im getting are all
free.

Thanks in advance, any help is much appreciated

Formula to see if a number is allocated
 

thanks a lot that worked a treat!


"Harald Staff" wrote:

Try

=ISNA(MATCH(A1,Sheet1!G:G,0))

HTH. Best wishes Harald

"Ernest Lai" skrev i melding
...
I need to find out if a number is allocated or free. Basically on sheet1
is
a load of information. in Column G on this sheet is a unique identifier
number.

In sheet2 I have a list of numbers say from 1-100 which correspond to the
identifier numbers in sheet1. Id like a forumla something like:

"If A1 in sheet 2 = any number in column G in sheet1, then print
Allocated,
else print free"

At the moment I have: =IF(A1=Sheet1!G:G,"allocated", "free")
But it does not work. What it is doing is If A1 = everything in column G
then say allocated, else say free. So the results im getting are all
free.

Thanks in advance, any help is much appreciated