Using array formula to populate data

[Jbuff26]

I’ve tried searching for help on this but everything I find is way more complicated than what I need and I just get lost following along. This is what I’m looking for:

If table titled “Applicants” on worksheet “Applicants” has a “Yes” in column I (Column I is titled “Offer” with the header in I1), then I want the name of the applicant in Column A (Titled “Name”) to Populate to Table “Hired” on worksheet “Hired” into Column A (titled “Name”).

Data range for table “Applicants” is A1:K69 with A1-K1 being the column headers.

Table “Hired” headers are A1-G1 with the first input for Column A being A2. Columns B-G is all manual entry.

I’ve tried to do this myself with other explanations/videos but simply cannot translate the examples to my own table. Also, I want to actually learn how this is done and am not looking for a copy and paste formula. So if someone is able to explain exactly what is happening and what each part of the formula is doing that would be fantastic!

[#2]

@Jbuff26 Let's begin be determining which one of the two solutions in the attached workbook work for you. If it's the dynamic array formula with the FILTER function, your'e lucky as it is very easy to apply. If not, then perhaps a combination of the INDEX, SMALL and IF functions might work.

[#3]

I attempted using the FILTER formula and I received the error #SPILL! I guess the FILTER formula on its own though will not work for me since it wants to return all data not just a single piece, right? This is why you mention using dynamic array formula?

[#4]

@Jbuff26 Actually, that's good news. The spill error means it's working but you have cell occupying the range where the FILTER functions wants to write the results to.

Remove anything that is inside the dashed area that comes up when you select the cell with #SPILL!

[#5]

Ok yes I see. I was trying to return the result to the first open cell in the “hired table”. So again, this is return EVERY piece of data from all columns in the Applicants table. How do I further refine to ONLY return the name to my HIRED table?

[#6 · Accepted solution]

I figured it out! Combined it with INDEX to return only the Name column. The spill error was happening because it didn’t like that I was trying to insert into a table which I wasn’t sure how to make that work. I’ll leave that for another day though!