3 Exact match criteria returning true formula help needed

crun2075

New Member
Joined
Jun 6, 2020
Messages
18
Office Version
  1. 365
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
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.
 
Upvote 0
Awesome thus far and thanks!

Its there a way to use this in a Vlookup so that if returned true a sentence populates?
 
Upvote 0
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.
 
Upvote 0
Appreciate the help thus far getting so close so with


Criteria 1Criteria 2Criteria 3ResultUser EnteredResult
995LLC0ind = 0994TRUE
994LLB1ind = 1LLB
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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)),"")
 
Upvote 0
Yes, Yes all the yes, thank you so much! Was curious what the 15,6 did however? Cant thank you enough!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top