3 Exact match criteria returning true formula help needed

[crun2075]

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.

 

[jasonb75]

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]

Awesome thus far and thanks!

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

 

[jasonb75]

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]

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]

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]

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]

I see what you mean now, does this help?

Book2
	A	B	C	D	E	F	G
1	Criteria 1	Criteria 2	Criteria 3	Result		User Entered	Result
2	995	LLC	0	ind = 0		994	ind = 1
3	994	LLB	1	ind = 1		LLB
4						1
Sheet1

Cell Formulas
Range	Formula
G2	G2	=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]

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

 

[jasonb75]

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.