Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
LookUp with Mulitple Criteria
I am trying to look up values based on three criteria Area, Item Number and Year. The data is organized in 18 columns (Area, Item, Year (3 others not relevent) and then 12 Months) and 200 + rows (which can grow each month) the rows can be different years but same Item and Area. I want to pull out the numbers from 2006 only. I have tried; =IF((AND($A11=$B$4,$G11=2006)),VLOOKUP($U2,$C$14:$ S$23,6,FALSE),0) -references are not correct if looking at attachment- and Index Match function as an array and can't get it to work. I could just delete 2005 data but the raw data comes formatted the same every month. A slice of the data is attached. Any help would be apprciated. +-------------------------------------------------------------------+ |Filename: MulitLookExamp.zip | |Download: http://www.excelforum.com/attachment.php?postid=5020 | +-------------------------------------------------------------------+ -- TimR ------------------------------------------------------------------------ TimR's Profile: http://www.excelforum.com/member.php...o&userid=27843 View this thread: http://www.excelforum.com/showthread...hreadid=560850 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
LookUp with Mulitple Criteria
Try entered with Ctrl-Shift-Enter:
=INDEX($G$4:$R$13,MATCH(1,($D$4:$D$13=$D16)*($F$4: $F$13=2006),0),MONTH(S15)) You may need to change $D16 and 2006 to reference your "Correct" cells but this gives correct reults for Jan, Feb, Mar etc(copy along). HTH "TimR" wrote: I am trying to look up values based on three criteria Area, Item Number and Year. The data is organized in 18 columns (Area, Item, Year (3 others not relevent) and then 12 Months) and 200 + rows (which can grow each month) the rows can be different years but same Item and Area. I want to pull out the numbers from 2006 only. I have tried; =IF((AND($A11=$B$4,$G11=2006)),VLOOKUP($U2,$C$14:$ S$23,6,FALSE),0) -references are not correct if looking at attachment- and Index Match function as an array and can't get it to work. I could just delete 2005 data but the raw data comes formatted the same every month. A slice of the data is attached. Any help would be apprciated. +-------------------------------------------------------------------+ |Filename: MulitLookExamp.zip | |Download: http://www.excelforum.com/attachment.php?postid=5020 | +-------------------------------------------------------------------+ -- TimR ------------------------------------------------------------------------ TimR's Profile: http://www.excelforum.com/member.php...o&userid=27843 View this thread: http://www.excelforum.com/showthread...hreadid=560850 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
LookUp with Mulitple Criteria
.....better ...
=INDEX($G$4:$R$13,MATCH(1,($D$4:$D$13=$D16)*($F$4: $F$13=YEAR(R$15)),0),MONTH(R$15)) Entered as before with Control-Shift-Enter "Toppers" wrote: Try entered with Ctrl-Shift-Enter: =INDEX($G$4:$R$13,MATCH(1,($D$4:$D$13=$D16)*($F$4: $F$13=2006),0),MONTH(S15)) You may need to change $D16 and 2006 to reference your "Correct" cells but this gives correct reults for Jan, Feb, Mar etc(copy along). HTH "TimR" wrote: I am trying to look up values based on three criteria Area, Item Number and Year. The data is organized in 18 columns (Area, Item, Year (3 others not relevent) and then 12 Months) and 200 + rows (which can grow each month) the rows can be different years but same Item and Area. I want to pull out the numbers from 2006 only. I have tried; =IF((AND($A11=$B$4,$G11=2006)),VLOOKUP($U2,$C$14:$ S$23,6,FALSE),0) -references are not correct if looking at attachment- and Index Match function as an array and can't get it to work. I could just delete 2005 data but the raw data comes formatted the same every month. A slice of the data is attached. Any help would be apprciated. +-------------------------------------------------------------------+ |Filename: MulitLookExamp.zip | |Download: http://www.excelforum.com/attachment.php?postid=5020 | +-------------------------------------------------------------------+ -- TimR ------------------------------------------------------------------------ TimR's Profile: http://www.excelforum.com/member.php...o&userid=27843 View this thread: http://www.excelforum.com/showthread...hreadid=560850 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
LookUp with Mulitple Criteria
How would I add a third (or 4th) critera I also need to be specific with which area the item was associated with I tried; =INDEX($G$4:$R$13,MATCH(1,($D$4:$D$13=$D16)*($F$4: $F$13=YEAR(R$15)),0),MONTH(R$15)*($A$:A$13="Etch Strip")) Then changing the value in A5 and the result didn't change? -- TimR ------------------------------------------------------------------------ TimR's Profile: http://www.excelforum.com/member.php...o&userid=27843 View this thread: http://www.excelforum.com/showthread...hreadid=560850 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
LookUp with Mulitple Criteria
Thanks Toppers! My error. I added another criteria. Thanks for your help -- TimR ------------------------------------------------------------------------ TimR's Profile: http://www.excelforum.com/member.php...o&userid=27843 View this thread: http://www.excelforum.com/showthread...hreadid=560850 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return single value on multipl criteria lookup | Excel Worksheet Functions | |||
Lookup Multiple Criteria return One answer | Excel Worksheet Functions | |||
Multiple criteria LOOKUP | Excel Worksheet Functions | |||
Lookup: 2 criteria | Excel Discussion (Misc queries) | |||
Conditional Lookup on Multiple Criteria | Excel Worksheet Functions |