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

	A	B
1	Country	England
2	Region	South-West

<tbody>
</tbody>

	A	B
4	Country and Region Match?	Yes

<tbody>
</tbody>

Sheet 2

	A	B	C
	Record	Country	Region
1	00001	England	South-West
2	00002	England	North-East
3	00003	England	North-West
4	00004	Scotland	West
5	00005	Wales	North
6	00006	Wales	East

<tbody>
</tbody>

 

[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.