Need help with a formula

New guy via OfficeKB.com · External Since: Dec 19, 2006 Posts: 1

Good Morning

I'm trying to create a formula that will compare one cell to another column
on a different page.

Lets say i have the PO A999-9999 on Wrksht 2 I want to compare this to column
L on Wrksht 1 and if it finds a match say true.

I have tried =L2='Worksheet1'!L2:L2456
also =L2='Worksheet1'!L:L

It just keeps saying false and there are a couple that I know is true... so
can anyone help me out with this asap? thanks!!

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-links/200612/1

RichardSchollar · External Since: Jun 07, 2006 Posts: 153

OK assuming your data on Wrksht2 is in column A (adjust as appropriate)
then on Wrksht 2 you'd use a formula like:

=ISNUMBER(MATCH(Wrksht2!A2,Wrksht1!$L:$L,0))

which will return True if the exact number is also on Wrksht1 col L or
False if it isn't.

Hope this helps!

Richard

New guy via OfficeKB.com wrote:
> Good Morning
>
> I'm trying to create a formula that will compare one cell to another column
> on a different page.
>
> Lets say i have the PO A999-9999 on Wrksht 2 I want to compare this to column
> L on Wrksht 1 and if it finds a match say true.
>
> I have tried =L2='Worksheet1'!L2:L2456
> also =L2='Worksheet1'!L:L
>
> It just keeps saying false and there are a couple that I know is true... so
> can anyone help me out with this asap? thanks!!
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.aspx/excel-links/200612/1

Hinojosa via OfficeKB.com · External Since: Dec 19, 2006 Posts: 4

I tried that and it's doing the same thing that it was doing with mine
let say we have have to copy it down 500 times it will do this

=ISNUMBER(MATCH('Wrksht2'!A2,'Wrksht1'!$L2:$L500,0))
=ISNUMBER(MATCH('Wrksht2'!A3,'Wrksht1'!$L3:$L501,0))
=ISNUMBER(MATCH('Wrksht2'!A4,'Wrksht1'!$L4:$L502,0))
=ISNUMBER(MATCH('Wrksht2'!A5,'Wrksht1'!$L5:$L503,0))

And so on...

RichardSchollar wrote:
>OK assuming your data on Wrksht2 is in column A (adjust as appropriate)
>then on Wrksht 2 you'd use a formula like:
>
>=ISNUMBER(MATCH(Wrksht2!A2,Wrksht1!$L:$L,0))
>
>which will return True if the exact number is also on Wrksht1 col L or
>False if it isn't.
>
>Hope this helps!
>
>Richard
>
>> Good Morning
>>
>[quoted text clipped - 13 lines]
>> Message posted via OfficeKB.com
>> http://www.officekb.com/Uwe/Forums.aspx/excel-links/200612/1

--
Message posted via http://www.officekb.com

Max · External Since: Mar 17, 2004 Posts: 1619

You need to lock the lookup array, ie either use Richard's
>=ISNUMBER(MATCH(Wrksht2!A2,Wrksht1!$L:$L,0))

or, if for some reason the entire col L cannot be used,
and the lookup range is 'Wrksht1'!L2:L500
then put it in the top cell as:
=ISNUMBER(MATCH('Wrksht2'!A2,'Wrksht1'!$L$2:$L$500,0))
and copy down

The $ signs in: 'Wrksht1'!$L$2:$L$500
will ensure that the lookup range is locked when you copy down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Hinojosa via OfficeKB.com" wrote:
> I tried that and it's doing the same thing that it was doing with mine
> let say we have have to copy it down 500 times it will do this
>
> =ISNUMBER(MATCH('Wrksht2'!A2,'Wrksht1'!$L2:$L500,0))
> =ISNUMBER(MATCH('Wrksht2'!A3,'Wrksht1'!$L3:$L501,0))
> =ISNUMBER(MATCH('Wrksht2'!A4,'Wrksht1'!$L4:$L502,0))
> =ISNUMBER(MATCH('Wrksht2'!A5,'Wrksht1'!$L5:$L503,0))
>
> And so on...

yayawilliam · External Since: Mar 17, 2007 Posts: 6

Confirmation: If you have reserved a seat to continue your flight in China,
or for a return journey on an international or regional flight, you should
reconfirm its reservation at least 72 hours before departure if you'll stop
over at the boarding place for longer than 72 hours. Or else the seat shall
be automatically cancelled. No confirmation is necessary if you stop over at
the place for your continued flight or return flight for less than 72
hours.Ticket Validity: The validity period of normal fare tickets, whether
for single, return or circular trips, is one year. The validity periods of
special fare tickets and discount normal fare tickets are calculated on the
basis of related fares in accordance with relevant stipulations.
http://mytravel-log.blogspot.com/