Home

Need a Formula for Pulling a Cell when Two Cells Match, and an IF Function Searches for Criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-755432%22%20slang%3D%22en-US%22%3ENeed%20a%20Formula%20for%20Pulling%20a%20Cell%20when%20Two%20Cells%20Match%2C%20and%20an%20IF%20Function%20Searches%20for%20Criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-755432%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20my%20first%20time%20posting%20here%2C%20so%20if%20I%20am%20missing%20anything%20in%20my%20question%20please%20let%20me%20know%20and%20I%20will%20add%20what%20is%20needed%20so%20that%20I%20can%20hopefully%20get%20some%20help%20on%20my%20issue.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20here's%20what%20I%20need%20help%20with%2C%20I%20have%20already%20tried%20a%20few%20formulas%20so%20far%20but%20no%20success.%20I%20want%20to%20pull%20the%20value%20in%20Column%20A%20of%20my%20spreadsheet%2C%20after%20checking%20Column%20H%20for%20certain%20text%2C%20and%20matching%20two%20other%20Columns%20(B%26amp%3BC)%2C%20BUT%2C%20only%20pull%20this%20value%20if%20the%20text%20in%20Column%20H%20is%20a%20different%20text.%20What%20I%20mean%20by%20this%20I%20will%20portray%20in%20the%20following%20example%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20A%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Column%20B%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Column%20C%26nbsp%3B%20%26nbsp%3B%20...%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Column%20H%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Column%20N%3C%2FP%3E%3CP%3EProduct%201%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Property%201%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BProperty%202%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BPermit%20Level%201%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BPermitted%20Name%3C%2FP%3E%3CP%3EProduct%202%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Property%201%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BProperty%202%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BPermit%20Exemption%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BPermitted%20Name%3C%2FP%3E%3CP%3EProduct%203%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Property%201%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BProperty%202%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BPermit%20Exemption%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BPermitted%20Name%3C%2FP%3E%3CP%3EProduct%204%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Property%201%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BProperty%202%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BPermit%20Level%201%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BPermitted%20Name%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20what%20I'm%20asking%20here%20is%20this%3B%20if%20I%20have%20a%20Product%20in%20Column%20A%20which%20is%20a%20Permit%20Exemption%20in%20Column%20H%2C%20I%20want%20to%20pull%20it%20over%20to%20Column%20N.%20I%20can%20only%20find%20the%20exact%20match%20based%20on%20the%20properties%20in%20Columns%20B%20and%20C%2C%20so%20I%20need%20to%20match%20these%20properties%2C%20and%20I%20need%20to%20pull%20the%20product%20name%20from%20a%20product%20that%20IS%20on%20a%20current%20permit%20level%20(i.e.%20Permit%20Level%201).%20So%20if%20Product%202%20is%20a%20Permit%20Exemption%20from%20Product%201%20which%20is%20on%20the%20Permit%2C%20I%20want%20to%20search%20for%20this%20by%20matching%20the%20two%20Properties%20that%20will%20be%20unique%20to%20this%20product.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20far%20I%20have%20tried%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%7B%3DIF(%24H1%3D%22Permit%20Exemption%22%2C(INDEX(%24A%242%3A%24A%249999%2CMATCH(%24B1%26amp%3B%24C1%2C%24B%3A%24B%26amp%3B%24C%3A%24C%2C0)))%2C%22%20%22)%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20issue%20here%20is%20this%20does%20not%20include%20the%20requirement%20to%20do%20this%20search%20for%20cells%20containing%20%22Permit%20Level%201%22%20AFTER%20determining%20that%20it%20needs%20the%20search%20because%20it%20is%20a%20%22Permit%20Exemption%22%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20provide%20any%20help%3F%20Sorry%20if%20this%20is%20a%20confusing%20request%2C%20I%20just%20can't%20wrap%20my%20head%20around%20how%20to%20do%20this%20formula%20while%20looking%20up%20two%20different%20criteria%20in%20the%20same%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E~Chris%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-755432%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-755766%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20a%20Formula%20for%20Pulling%20a%20Cell%20when%20Two%20Cells%20Match%2C%20and%20an%20IF%20Function%20Searches%20for%20Criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-755766%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3BChris%3C%2FP%3E%0A%3CP%3EIf%20I%20understood%20you%20correctly%2C%20you%20might%20consider%20a%20formula%20in%20cell%20N1%20like%3A%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(IF(%24H1%3D%22Permit%20Exemption%22%2CINDEX(%24A%242%3A%24A%249999%2CAGGREGATE(15%2C6%2C(ROW(%24A%242%3A%24A%249999)-ROW(%24A%242)%2B1)%2F((%24B%242%3A%24B%249999%3D%24B1)*(%24C%242%3A%24C%249999%3D%24C1)*(%24H%242%3A%24H%249999%3D%22Permit%20Level%201%22))%2C1))%2C%22%22)%2C%22%22)%3C%2FPRE%3E%0A%3CP%3EThis%20formula%20is%20triggered%20by%20Permit%20Exemption%20in%20cell%20H1.%20If%20found%2C%20it%20looks%20for%20the%20first%20value%20in%20column%20A%20where%20column%20H%20is%20Permit%20Level%201%20and%20columns%20B%20and%20C%20match%20the%20values%20from%20row%201.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20the%20value%20to%20be%20returned%20must%20be%20found%20in%20a%20row%20%3CEM%3E%3CSTRONG%3Ebelow%3C%2FSTRONG%3E%3C%2FEM%3Ethe%20one%20with%20the%20formula%2C%20you%20will%20need%20to%20remove%20some%20%24%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(IF(%24H1%3D%22Permit%20Exemption%22%2CINDEX(%24A2%3A%24A%249999%2CAGGREGATE(15%2C6%2C(ROW(%24A2%3A%24A%249999)-ROW(%24A2)%2B1)%2F((%24B2%3A%24B%249999%3D%24B1)*(%24C2%3A%24C%249999%3D%24C1)*(%24H2%3A%24H%249999%3D%22Permit%20Level%201%22))%2C1))%2C%22%22)%2C%22%22)%3C%2FPRE%3E%0A%3CP%3EBoth%20formulas%20use%20the%20AGGREGATE%20function%2C%20and%20thereby%20avoid%20the%20need%20for%20array-entry.%20AGGREGATE%20was%20introduced%20with%20Excel%202010.%20If%20you%20have%20an%20earlier%20version%20of%20Excel%2C%20an%20alternative%20formula%20construction%20using%20SMALL%20is%20possible%20(requires%20array-entry).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-756520%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20a%20Formula%20for%20Pulling%20a%20Cell%20when%20Two%20Cells%20Match%2C%20and%20an%20IF%20Function%20Searches%20for%20Criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-756520%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F376670%22%20target%3D%22_blank%22%3E%40ChrisLizardi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20somehow%20minimize%20the%20confusion%2C%20please%20attach%20your%20sample%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E
ChrisLizardi
Occasional Visitor

Hi all,

 

This is my first time posting here, so if I am missing anything in my question please let me know and I will add what is needed so that I can hopefully get some help on my issue.

 

So here's what I need help with, I have already tried a few formulas so far but no success. I want to pull the value in Column A of my spreadsheet, after checking Column H for certain text, and matching two other Columns (B&C), BUT, only pull this value if the text in Column H is a different text. What I mean by this I will portray in the following example:

 

Column A      Column B      Column C    ...      Column H                          Column N

Product 1      Property 1     Property 2           Permit Level 1                   Permitted Name

Product 2      Property 1     Property 2           Permit Exemption             Permitted Name

Product 3      Property 1     Property 2           Permit Exemption             Permitted Name

Product 4      Property 1     Property 2           Permit Level 1                   Permitted Name

 

So what I'm asking here is this; if I have a Product in Column A which is a Permit Exemption in Column H, I want to pull it over to Column N. I can only find the exact match based on the properties in Columns B and C, so I need to match these properties, and I need to pull the product name from a product that IS on a current permit level (i.e. Permit Level 1). So if Product 2 is a Permit Exemption from Product 1 which is on the Permit, I want to search for this by matching the two Properties that will be unique to this product.

 

So far I have tried:

 

{=IF($H1="Permit Exemption",(INDEX($A$2:$A$9999,MATCH($B1&$C1,$B:$B&$C:$C,0)))," ")}

 

The issue here is this does not include the requirement to do this search for cells containing "Permit Level 1" AFTER determining that it needs the search because it is a "Permit Exemption" cell.

 

Can anyone provide any help? Sorry if this is a confusing request, I just can't wrap my head around how to do this formula while looking up two different criteria in the same column.

 

Thanks,

~Chris 

2 Replies

 Chris

If I understood you correctly, you might consider a formula in cell N1 like:

=IFERROR(IF($H1="Permit Exemption",INDEX($A$2:$A$9999,AGGREGATE(15,6,(ROW($A$2:$A$9999)-ROW($A$2)+1)/(($B$2:$B$9999=$B1)*($C$2:$C$9999=$C1)*($H$2:$H$9999="Permit Level 1")),1)),""),"")

This formula is triggered by Permit Exemption in cell H1. If found, it looks for the first value in column A where column H is Permit Level 1 and columns B and C match the values from row 1.

 

If the value to be returned must be found in a row below the one with the formula, you will need to remove some $

=IFERROR(IF($H1="Permit Exemption",INDEX($A2:$A$9999,AGGREGATE(15,6,(ROW($A2:$A$9999)-ROW($A2)+1)/(($B2:$B$9999=$B1)*($C2:$C$9999=$C1)*($H2:$H$9999="Permit Level 1")),1)),""),"")

Both formulas use the AGGREGATE function, and thereby avoid the need for array-entry. AGGREGATE was introduced with Excel 2010. If you have an earlier version of Excel, an alternative formula construction using SMALL is possible (requires array-entry).

@ChrisLizardi 

To somehow minimize the confusion, please attach your sample file.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies