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

Try

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

HTH. Best wishes Harald

"Ernest Lai" <[email protected]> skrev i melding
news:[email protected]...
>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

thanks a lot that worked a treat!


"Harald Staff" wrote:

> Try
>
> =ISNA(MATCH(A1,Sheet1!G:G,0))
>
> HTH. Best wishes Harald
>
> "Ernest Lai" <[email protected]> skrev i melding
> news:[email protected]...
> >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
>
>
>