Lookup Doesn't Work
 

I am using lookup but and it works for maybe 100 to 150 rows but if it
is 254 it doesn't work for me.
I am looking up this value 238355 which is my 2 and date of 01/03/05 in
F2
so I am searching A2:A100 which works (result is 12/27/04) but if I go
to A254 it doesn't work. I get the last date value of C254. How can I
accomplish this part.

=LOOKUP(CONCATENATE("2",F2),A2:A100,C2:C100)

138344 1 12/23/04
138377 1 01/25/05
138406 1 02/23/05
138435 1 03/24/05
138467 1 04/25/05
138496 1 05/24/05
138526 1 06/23/05
138558 1 07/25/05
138587 1 08/23/05
138617 1 09/22/05
138646 1 10/21/05
138677 1 11/21/05
238348 2 12/27/04
238378 2 01/26/05
238407 2 02/24/05
238439 2 03/28/05
238468 2 04/26/05
238497 2 05/25/05
238527 2 06/24/05
238559 2 07/26/05
238588 2 08/24/05
238618 2 09/23/05
238649 2 10/24/05
238678 2 11/22/05


--
spalmarez
------------------------------------------------------------------------
spalmarez's Profile: http://www.excelforum.com/member.php...o&userid=15961
View this thread: http://www.excelforum.com/showthread...hreadid=276426

Hi
use
=VLOOKUP("2" &F2,A2:C100,3,0)


--
Regards
Frank Kabel
Frankfurt, Germany

"spalmarez" schrieb im
Newsbeitrag ...

I am using lookup but and it works for maybe 100 to 150 rows but if
it
is 254 it doesn't work for me.
I am looking up this value 238355 which is my 2 and date of 01/03/05
in
F2
so I am searching A2:A100 which works (result is 12/27/04) but if I
go
to A254 it doesn't work. I get the last date value of C254. How can I
accomplish this part.

=LOOKUP(CONCATENATE("2",F2),A2:A100,C2:C100)

138344 1 12/23/04
138377 1 01/25/05
138406 1 02/23/05
138435 1 03/24/05
138467 1 04/25/05
138496 1 05/24/05
138526 1 06/23/05
138558 1 07/25/05
138587 1 08/23/05
138617 1 09/22/05
138646 1 10/21/05
138677 1 11/21/05
238348 2 12/27/04
238378 2 01/26/05
238407 2 02/24/05
238439 2 03/28/05
238468 2 04/26/05
238497 2 05/25/05
238527 2 06/24/05
238559 2 07/26/05
238588 2 08/24/05
238618 2 09/23/05
238649 2 10/24/05
238678 2 11/22/05


--
spalmarez
---------------------------------------------------------------------
---
spalmarez's Profile:
http://www.excelforum.com/member.php...o&userid=15961
View this thread:
http://www.excelforum.com/showthread...hreadid=276426

Assuming the 138344s are numbers then try:-

=SUMPRODUCT((B2:B100=2)*(C2:C100=F2)*(A2:A100))

Sumproduct allows you to specify more than one criteria in the arguments

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"spalmarez" wrote in message
...

I am using lookup but and it works for maybe 100 to 150 rows but if it
is 254 it doesn't work for me.
I am looking up this value 238355 which is my 2 and date of 01/03/05 in
F2
so I am searching A2:A100 which works (result is 12/27/04) but if I go
to A254 it doesn't work. I get the last date value of C254. How can I
accomplish this part.

=LOOKUP(CONCATENATE("2",F2),A2:A100,C2:C100)

138344 1 12/23/04
138377 1 01/25/05
138406 1 02/23/05
138435 1 03/24/05
138467 1 04/25/05
138496 1 05/24/05
138526 1 06/23/05
138558 1 07/25/05
138587 1 08/23/05
138617 1 09/22/05
138646 1 10/21/05
138677 1 11/21/05
238348 2 12/27/04
238378 2 01/26/05
238407 2 02/24/05
238439 2 03/28/05
238468 2 04/26/05
238497 2 05/25/05
238527 2 06/24/05
238559 2 07/26/05
238588 2 08/24/05
238618 2 09/23/05
238649 2 10/24/05
238678 2 11/22/05


--
spalmarez
------------------------------------------------------------------------
spalmarez's Profile:
http://www.excelforum.com/member.php...o&userid=15961
View this thread: http://www.excelforum.com/showthread...hreadid=276426



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.789 / Virus Database: 534 - Release Date: 07/11/2004

spalmarez Wrote:
I don't think I explained my description correctly.

This Lookup function works returns value 238348 (I am looking for
238355):
=LOOKUP(CONCATENATE("2",F2),A2:A100,C2:C100)

I would like to do this but it doesn't work. It returns the value in
row 254 (I am looking for 23855), My return value should be 238348 but
it isn't. Why would it work up to 100 or so rows and not more rows?:

=LOOKUP(CONCATENATE("2",F2),A2:A254,C2:C254)

That formula cannot return either 238348 or 238355 since what it
returns is necessarily a date because of C2:C254.

Having said that, What does F2 house?


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=276426