Need help with a formula

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.a...links/200612/1

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.a...links/200612/1

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.a...links/200612/1

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

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...

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/