Index array list based on multiple matching Values

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Anyone know anything about this? Or can you direct me to a thread with an explanation.

Originally Posted by JohnTopley

Try

for each department sheet : assumes Data is organised as per your current DATA sheet)

in B8

=IF(ROWS($1:1)<=COUNTIF(Data!$A$1:$A$1000,$B$2),INDEX(Data!B$1:B$1000,MATCH($B$2,Data!$A$1:$A$1000,0)+ROWS($1:1)-1),"")

Copy down and across to D

if F8

=IF(ROWS($1:1)<=COUNTIF(Data!$A$1:$A$1000,$B$2),INDEX(Data!E$1:E$1000,MATCH($B$2,Data!$A$1:$A$1000,0)+ROWS($1:1)-1),"")

See "Dept_0" / "Dept_1" in attached.

It appears to be working perfectly! You're a life saver!

Follow up question: The previous formula worked perfectly for what I needed. Now I am trying to pull in more information in a similar way, but I can't figure out how to modify the formula to get it to work. I had it working for a minute, but then it would stop importing information after the first 4 rows.

My goal is to have each of the department sheets autofill columns K:AH from "Master" columns H:AE, based off of the equipment# (B), then Model description (G). It may not seem obvious why I need to do this because I have simplified the information in the sheets. Normally there are multiple equipment specs for each equipment number, so I need to filter the results to get the correct specs.

The Dept_0 and Dept_1 sheets have the formula I tried to use. The Goal sheet has (more or less) what I am trying to achieve. The columns that I need to filter by are in pink and the columns I need to fill are under the blue header.

Thank you!

Originally Posted by JeteMc

I put the following in K8 of the 'Goal' sheet, dragged the fill handle over to AH8 then down to AH30. I got the same values as were previously manually placed in that range, so I used the same formula on the 'Dept_0' and 'Dept_1' sheets and got results that seemed reasonable.

Formula:

=IFERROR(INDEX(Master!H$4:H$500,AGGREGATE(15,6,(ROW($4:$500)-3)/((Master!$A$4:$A$500=$B8)*(Master!$D$4:$D$500=$G8)),1)),"")

Let us know if you have any questions.

You're awesome! I plugged it in and it seems to work perfectly!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.