IF Two or more values found in range on same row

[milleniummanp7]

Hey Folks,

Looking for some help with a bit of logic I'm stuck on. I've done this before, and for the life of me I can't remember how.

In Sheet 1, I have two cells that I populate manually with the values I'm looking for. In the case of the example I've provided, I've said I'm looking for country England, and region south-west.
Also in Sheet 1, down below this table I have a Summary table. It's referencing Sheet 2, with the intention being to search the table and return Yes or No based on whether a single record matches the criteria.

So I've set the country to England and the Region to South-West.
In sheet two, record 00001 matches this criteria. Therefore, I want to return Yes in the country and region match field.

Essentially, I'm saying IF Sheet1!B1 is found in Sheet2!B:B and Sheet1!B2 is found in Sheet2!C:C on the same same row in Sheet then Sheet1!B4 is Yes

Can anyone explain how I can do this? I think it's a combo of INDEX/MATCH but really stuck here.

Many thanks!



Sheet 1
[TABLE="width: 300"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Country[/TD]
[TD]England[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Region[/TD]
[TD]South-West[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Country and Region Match?[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Record[/TD]
[TD]Country[/TD]
[TD]Region[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]00001[/TD]
[TD]England[/TD]
[TD]South-West[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]00002[/TD]
[TD]England[/TD]
[TD]North-East[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]00003[/TD]
[TD]England[/TD]
[TD]North-West[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]00004[/TD]
[TD]Scotland[/TD]
[TD]West[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]00005[/TD]
[TD]Wales[/TD]
[TD]North[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]00006[/TD]
[TD]Wales[/TD]
[TD]East[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Try

B4
=IF(COUNTIFS(Sheet2!B:B,B1,Sheet2!C:C,B2),"Yes","No")

M.

 

[milleniummanp7]

Try

B4
=IF(COUNTIFS(Sheet2!B:B,B1,Sheet2!C:C,B2),"Yes","No")

M.

Thank you! COUNTIFS works, and I'm actually a little ashamed it was that easy!

Follow up question - what's the correct syntax for doing a >= or <= in a COUNTIF using a cell value?

I made this formula, saying if column AC = W and the value in column T is between 5.6 and 6.6, then count.

=COUNTIFS(Sheet1!T$6:T$1000,">=5.6",Sheet1!T$6:T$1000,"<=6.6",Sheet1!AC$6:AC$1000,"W")

However I can only get it working using the manually entered 5.6/6.6. What I'm actually doing is a margin of error calculation
On my current sheet I have a cell H3 which contains a manually entered value, 6.1. I want the countif to return all values +/- 0.5 of that manually entered value (between 5.6 and 6.1 in this case).

So instead of the manually entered values, I'm looking for something like below. Problem is, the (H3+0.5) doesn't work because it's in quotations. If I take away the quotations, or just try to put the quotations around the ">=", the formula won't enter because of an error.

=COUNTIFS(Sheet1!T$6:T$1000,">=(H3-0.5)",Sheet1!T$6:T$1000,"<=(H3+0.5)",Sheet1!AC$6:AC$1000,"W")

 

[Marcelo Branco]

Try

=COUNTIFS(Sheet1!T$6:T$1000,">="&H3-0.5,Sheet1!T$6:T$1000,"<="&H3+0.5,Sheet1!AC$6:AC$1000,"W")

M.