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

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?

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?

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?

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