• 466K Members
• 8,594 Online
• 563K Conversations

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

Occasional Visitor

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

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

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

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).

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

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