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
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies