Find row that matches criteria in two columns

[joshbne]

I created a workbook that displays the site permit status of employees for various types of vehicles.
I have found a formula that works (below) but is very slow.
It finds a row in the sheet “PMPQueryAll” that matches both the employee name in cell A2 to those listed in column C of “PMPQueryAll” and the permit type in B1 to those listed in column I of “PMP QueryAll”. It then displays the status from column F of “PMPQueryAll”.
It is a rather large data set and I think this formula creates loops wihich make it really slow. Is there a better way?
It’s also important to note that there are multiple entries for each employee and there is more than one employee for each permit type.

=IFNA(INDIRECT("PMPQueryAll!$F"&MATCH(1,INDEX((PMPQueryAll!$C:$C=$A2)*(PMPQueryAll!$I:$I=B$1),0),0)),"")

[Kaper]

try using index instead of indirect. Recalculation here is by far more "greedy":

Please Login or Register  to view this content.

[joshbne]

[QUOTE=Kaper;4996621]try using index instead of indirect. Recalculation here is by far more "greedy"
Thanks!, this helped quite a bit.
I’ll leave the thread open to see if there are any further suggestions.

[Kaper]

It's always easier to analyze formula in it's natural environment, so in a worksheet.
To increase chance for further help consider attachng test workbook (some 10-20 rows of data is usually enough)

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. (If there are typical cases like: all unique values/duplicates could occur, day/night, nobody present/several persons at once, before/on/past due, etc. - please show them all or at least indicate in text) The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution(s) is/are also shown (mock up the results manually).

3. Make sure that all confidential/restricted information (either personal or business) like real e-mails, social security numbers, bank accounts, etc. is removed first!!

To attach an Excel file you have to do the following: Just before posting, scroll down and press Go Advanced button and then scroll down and press Manage Attachments link. Now follow the instructions at the top of that pop-up screen.