# 3 Exact match criteria returning true formula help needed

#### crun2075

##### New Member
Hello all first time posting here but have visited and learned a lot.

I have the following situation I'm trying to figure out and I'm completely stuck on how to return true via a 3 criteria match.

Lets say I have 2 Rows and 3 Columns:

995 LLC 0
996 LLL 1

If the inquiry entered matches the above data return true, however it must match via the row, ex: 995 LLC and 0 return true, else false.

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### jasonb75

##### Well-known Member
Hello and welcome first time poster

If you do simply just need a TRUE / FALSE result, i.e to verify that a match exists, then the easiest way would be to use countifs.

=COUNTIFS(column1,995,column2,"LLC",column3,0)>0

Using >0 at the end will give you TRUE for a count greater than 0 instead of the actual count. A count of 0 will return FALSE.

#### crun2075

##### New Member
Awesome thus far and thanks!

Its there a way to use this in a Vlookup so that if returned true a sentence populates?

#### jasonb75

##### Well-known Member
No, that is a totally different animal. If it is the same sentence regardless of criteria then you could use it with IF, e.g. =IF(COUNTIFS(.....)>0,"Sentence goes here","") but if you need a proper lookup to return a unique sentence based on the criteria selected then you would need something like

=IFERROR(INDEX(sentence column,AGGREGATE(15,6,ROW(sentence range)/(range1=995)/(range2="LLC")/(range3=0),1),"")

Note that with this method, the part in bold refers to the whole columns, e.g. A:A, The other sections refer to the range with data, e.g. A\$2:A\$200. Setting this up incorrectly will most likely mean the sentence returned by the formula is not the correct one.

#### crun2075

##### New Member

Appreciate the help thus far getting so close so with

 Criteria 1 Criteria 2 Criteria 3 Result User Entered Result 995 LLC 0 ind = 0 994 TRUE 994 LLB 1 ind = 1 LLB 1

=COUNTIFS(K2:K3,P2,L2:L3,P3,M2:M3,P4)>0 is returning true so close! The above formula was a bit complicated for me so use the one to the left if true can we return the matching result column?

Sorry should have provided the above table earlier.

#### jasonb75

##### Well-known Member
How does the 'sentence' that you want to return relate to the table?

It's better to provide an example of everything (that is relevant to the question) rather than just bits of it.

From the above table, my answer would be =AND(criteria 1 = 994, criteria 2 = "LLB", criteria 3 = 1) but that will not give you an associated result without extra manipulation.

#### crun2075

##### New Member

Sorry even having have a hard time even explaining it....Still a very new novice to excel. So in the above table. If the user Entered the values under the user entered column, and if they were to match row 1-3 the result would be ind =0.

#### jasonb75

##### Well-known Member
I see what you mean now, does this help?

Book2
ABCDEFG
1Criteria 1Criteria 2Criteria 3ResultUser EnteredResult
2995LLC0ind = 0994ind = 1
3994LLB1ind = 1LLB
41
Sheet1
Cell Formulas
RangeFormula
G2G2=IFERROR(INDEX(D:D,AGGREGATE(15,6,ROW(\$D\$2:\$D\$3)/(\$A\$2:\$A\$3=\$F\$2)/(\$B\$2:\$B\$3=\$F\$3)/(\$C\$2:\$C\$3=\$F\$4),1)),"")

#### crun2075

##### New Member
Yes, Yes all the yes, thank you so much! Was curious what the 15,6 did however? Cant thank you enough!

#### jasonb75

##### Well-known Member
15 and 6 are specific to the AGGREGATE function, telling it what it should do with the information that it is processing.

The numbers that can be used for the various options are all listed in help, but as you're new to excel I would suggest learning some less complicate functions first.

Replies
1
Views
100
Replies
1
Views
96
Replies
5
Views
226
Replies
14
Views
143
Replies
1
Views
154

1,130,061
Messages
5,639,830
Members
417,117
Latest member
Ravi Pandey

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back