3 Exact match criteria returning true formula help needed

crun2075

New Member
Joined
Jun 6, 2020
Messages
14
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.
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,479
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 6, 2020
Messages
14
Office Version
  1. 365
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
Joined
Dec 30, 2008
Messages
12,479
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 6, 2020
Messages
14
Office Version
  1. 365

ADVERTISEMENT

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.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,479
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 6, 2020
Messages
14
Office Version
  1. 365

ADVERTISEMENT

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
Joined
Dec 30, 2008
Messages
12,479
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 6, 2020
Messages
14
Office Version
  1. 365
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
Joined
Dec 30, 2008
Messages
12,479
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,696
Messages
5,626,361
Members
416,175
Latest member
LavendarRabbit

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
Top