Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Service work order | Charts and Charting in Excel | |||
need check two worksheets to lookup a value | Excel Discussion (Misc queries) | |||
The colors should work properly in Shared Excel Workbooks. | Excel Worksheet Functions | |||
Lookup with logic | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |