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

[ChrisLizardi]

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]

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

[#3]

@ChrisLizardi 

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