Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If( AND( with VLOOKUP....
'=IF(AND(B2:B5=A1,C2:C5="WEEK1"),VLOOKUP(C10,$B$2: $D$5,3,FALSE),"") - this
formula gives me a #VALUE error. A1 = 10 A2 = 11 col B col C Col D Col E Col E STR_NBR WEEK1 TTOH RGOH MEMO 10 WEEK1 5363 4407 234 11 WEEK1 7424 5561 605 10 WEEK2 3160 2543 352 11 WEEK2 2655 1995 246 10 WEEK3 3160 2543 352 11 WEEK3 2655 1995 246 I will need to look up data from collumns D - E for 800 stores for 15 weeks. any suggestions? thank you in advance. jane |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If( AND( with VLOOKUP....
jane wrote:
'=IF(AND(B2:B5=A1,C2:C5="WEEK1"),VLOOKUP(C10,$B$2: $D$5,3,FALSE),"") - this formula gives me a #VALUE error. A1 = 10 A2 = 11 col B col C Col D Col E Col E STR_NBR WEEK1 TTOH RGOH MEMO 10 WEEK1 5363 4407 234 11 WEEK1 7424 5561 605 10 WEEK2 3160 2543 352 11 WEEK2 2655 1995 246 10 WEEK3 3160 2543 352 11 WEEK3 2655 1995 246 I will need to look up data from collumns D - E for 800 stores for 15 weeks. any suggestions? thank you in advance. jane Looks like this should be an array formula. Did you use CTRL+SHIFT+ENTER? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If( AND( with VLOOKUP....
I tried that Glenn - no error but came back with blank.... should have value
of 5363... "Glenn" wrote: jane wrote: '=IF(AND(B2:B5=A1,C2:C5="WEEK1"),VLOOKUP(C10,$B$2: $D$5,3,FALSE),"") - this formula gives me a #VALUE error. A1 = 10 A2 = 11 col B col C Col D Col E Col E STR_NBR WEEK1 TTOH RGOH MEMO 10 WEEK1 5363 4407 234 11 WEEK1 7424 5561 605 10 WEEK2 3160 2543 352 11 WEEK2 2655 1995 246 10 WEEK3 3160 2543 352 11 WEEK3 2655 1995 246 I will need to look up data from collumns D - E for 800 stores for 15 weeks. any suggestions? thank you in advance. jane Looks like this should be an array formula. Did you use CTRL+SHIFT+ENTER? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If( AND( with VLOOKUP....
Of course you're going to get blank as a result. Your formula requires that
every cell in b2:b5 equals a1, and every cell in c2:c5 equals "WEEK1" before it will do the Vlookup. As this is not the case, the If is false, giving you a blank result. Tell us what you want to accomplish, and someone will give you a proper formula. Regards, Fred. "jane" wrote in message ... I tried that Glenn - no error but came back with blank.... should have value of 5363... "Glenn" wrote: jane wrote: '=IF(AND(B2:B5=A1,C2:C5="WEEK1"),VLOOKUP(C10,$B$2: $D$5,3,FALSE),"") - this formula gives me a #VALUE error. A1 = 10 A2 = 11 col B col C Col D Col E Col E STR_NBR WEEK1 TTOH RGOH MEMO 10 WEEK1 5363 4407 234 11 WEEK1 7424 5561 605 10 WEEK2 3160 2543 352 11 WEEK2 2655 1995 246 10 WEEK3 3160 2543 352 11 WEEK3 2655 1995 246 I will need to look up data from collumns D - E for 800 stores for 15 weeks. any suggestions? thank you in advance. jane Looks like this should be an array formula. Did you use CTRL+SHIFT+ENTER? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If( AND( with VLOOKUP....
Your 3 formulas for getting the information for TTOH, RGOH, and MEMO are all
very similar. Just change the last range reference(d2:d100) to get RGOH and MEMO. This is the formula for TTOH. =SUMPRODUCT(--(B2:B100=A1),--(C2:C100="Week1"),(D2:D100)) I believe this is what you are looking for. -- John C "jane" wrote: '=IF(AND(B2:B5=A1,C2:C5="WEEK1"),VLOOKUP(C10,$B$2: $D$5,3,FALSE),"") - this formula gives me a #VALUE error. A1 = 10 A2 = 11 col B col C Col D Col E Col E STR_NBR WEEK1 TTOH RGOH MEMO 10 WEEK1 5363 4407 234 11 WEEK1 7424 5561 605 10 WEEK2 3160 2543 352 11 WEEK2 2655 1995 246 10 WEEK3 3160 2543 352 11 WEEK3 2655 1995 246 I will need to look up data from collumns D - E for 800 stores for 15 weeks. any suggestions? thank you in advance. jane |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |