Problem in Double match with AND() with range

**majidsiddique** · 02-13-2019, 02:47 AM

hi all,
can anybody tell me why this formula is not working.
=IF(AND($C$6=Sheet1!$D$4:R4,B7=Sheet1!B7:B15),VLOOKUP(B7,Sheet1!$B$7:$F$15,3,0),"0")
But when i use this, its working,
=IF(AND($C$6=Sheet1!$D$4,B7=Sheet1!B7),VLOOKUP(B7,Sheet1!$B$7:$F$15,3,0),"0")

**etaf** · 02-13-2019, 03:19 AM

you are using a range in the AND() that does not work
AND($C$6=Sheet1!$D$4:R4,B7=Sheet1!B7:B15)
why are you using a range , what are you trying to do exactly

if you want to see if the value in C6 exists in the Range D4:R4 and B7 in B7:B15
you would use a countif()
that will test for the existence

but B7 will always exist in B7:b15, As B7=B7 so TRUE

**Pepe Le Mokko** · 02-13-2019, 03:29 AM

Title adapted. much better. Thank you

**davsth** · 02-13-2019, 06:24 AM

You will get no assistance till you comply with the request from Pepe. Formula is not working isnt really a good item to search on

**majidsiddique** · 02-13-2019, 07:26 AM

Hi Pepe,
how someone can identify poor thread, its very simple if i have a problem in formula, so i create a thread with formula problem.

**davsth** · 02-13-2019, 10:56 AM

I think you have made an attempt to change the title.

=IF(countif(Sheet1!$D$4:R4,$C$6)*countif(Sheet1!B7:B15,B7),VLOOKUP(B7,Sheet1!$B$7:$F$15,3,0),"0")

**majidsiddique** · 02-14-2019, 12:57 AM

Hi Davsth,
thanks to reply, your formula is working good BUT tell me one thing if change date in specific cell how we lookup pick data increase AUTO column based on cell ref. see the attached file for more clarification. kindly tell me i control this cell ref by list-box button and tell me one thing why Hlookup is not working with name range. i use sheet2 Hlookup(C5,VL,A8,False) , VL is the range of sheet1 d4 to S65.
Thanks