Determine if all criteria fit between numerial ranges (for Excel and Google Sheets)

Here is the sample I have created.

Column N is the only column that has input values.
Column K is the result I want.

Column C, E, I is the least acceptable value, column D, G, J is the largest acceptable value.

K2 does not match because item1 is only acceptable at 2, while in columns M/N you see item1 is at 1. The items can repeat!!
K3 also does not match because item3 is only acceptable at values between 1 to 4, while in columns M/N you see item3 at 6.

So on.. All 3 of these critera have to be met in order to return a match as in K5.

Any help here, this seems impossible I've been at it all day... Thanks a lot!

Administrative Note:

Welcome to the forum.

Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

Thank you for helping us to help you.

Also, please clarify your version of Excel - is it Office 365?

I have no idea what locale I am using (it doesn't have to match physical location), but my user interface is English. I am also using online versions of excel (either google sheets or the excel in onedrive) so I have no clue what specific settings is being used.

What is your location? Is this query for Google Sheets or Excel? From what you have said, your Office version is 365 - please update your profile as requested. Thanks.

OK - so change your location field to Asia, please, and in the MS Version field, put Office 365 and Google Sheets.

There won't be. Locale issues can be at the root of problems some members will have, especially with date and currency formats, which is why it is useful for helpers to know roughly where you are in the world.

I looked at your workbook and could not understand the expected results - you are going to need to explain in more detail, please.

Column M are items, you will see that there are items in columns B, E, and H.
Column N are numerial values, you will see that there are numerial values in C, D, F, G, I, J.
The two numerial values beside each item is the min and max acceptable.

In row 2,
item1 is between 2-2, and you can see columns M/N item1 is 1. NO MATCH.
item1 is between 2-2, and you can see columns M/N item1 is 1. NO MATCH. (yes this is a repeat. normal.)
item2 is between 1-4, and you can see columns M/N item 2 is 2. MATCH.

Therefore K2 should return a NO MATCH because it has (a) criteria(s) that doesn't match.


If you go down to row 5 however. Every single item DOES match.
item5 is between 1-2, and you can see columns M/N item2 is 2. MATCH.
item3 is between 5-8, and you can see columns M/N item3 is 6. MATCH.
item2 is between 2-3, and you can see columns M/N item2 is 2. MATCH.

Therefor K5 should return a MATCH.


If I require more columns to easier put different steps of calculation, that is perfectly fine. The entire formula does not need to be in Column K.

This proposal employs three helper columns (M:O) which may be moved and/or hidden for aesthetic purposes.
The helper columns are populated using formulas similar to: =AND(INDEX($V$1:$V$5,MATCH(B2,$U$1:$U$5,0))>=C2,INDEX($V$1:$V$5,MATCH(B2,$U$1:$U$5,0))<=D2)
Note that in the file attached to post #1 some values (with green error markers) were text as opposed to numbers which has been changed in the file attached to this post.
Column K is populated using: =IF(COUNTIF(M2:O2,TRUE)=3,"MATCH","NO MATCH")
Note that regional settings may dictate that the commas should be changed to semicolons, although in the attached file that change should occur automatically.
Let us know if you have any questions.