Using INDEX + MATCH to Fill Data from one sheet to another.

I've done some searching around on this forum and via google to try and find a solution to my problem but I couldn't get anything I found to work with me.

I'm very new to using INDEX + MATCH to accomplish my Excel goals, but am very quickly learning it's value, and am pretty sure it's the only way (aside from VBA, but I don't want that), to get what I want accomplished.

I am looking to try and pull data from one worksheet into another based on the value of a single cell. This is easily accomplished using vlookup, but since the data sheet I'm pulling from has hundreds and hundreds of rows, and not everything will always be in order on the data sheet, vlookup leaves blank spaces throughout the entirety of my sheet. I want all matching data to be filled in at the top.

Via the spreadsheet I've attached, whenever Column B on 'DataSheet' changes to "New Work", I want the Work Order number to fill in automatically in column A on the New Work. I can use vlookup from there to fill in the rest of the information. With the way I've written up the formula, it doesn't populate past the first instance of "New Work", and I'm not sure how to have it add each instance only once.

This is my code.

Please Login or Register  to view this content.

What do I need to do?

All help is appreciated.

Try this regular formula in A21 on your New Work sheet and pull formula to the right and then down

adjust ranges if need it

=IFERROR(INDEX('Data Page'!A:A,AGGREGATE(15,6,ROW('Data Page'!$B$2:$B$100)/('Data Page'!$B$2:$B$100="New Work"),ROWS(A$1:A1))),"")

Originally Posted by SabbathXXL

On the actual spreadsheet I use it for, I only have it populating 50 rows

Hi.

By that do you mean that you've copied the formula down to a total of 50 rows?

And what about the references being passed to the function? How many rows' worth of data do they cover?

Regards

Thanks, but what about my other question?

Regards

Thanks.

In that case those formulas should take no time at all to calculate.

Could you just post one of them so that I can be doubly sure?

Regards

Originally Posted by XOR LX

And what about the references being passed to the function? How many rows' worth of data do they cover?

Originally Posted by SabbathXXL

At the moment, 232, but this number increases by 1-15 rows nearly every week.

Originally Posted by SabbathXXL

Please Login or Register  to view this content.

Not sure I understand. That formula is not referencing 232 rows' worth of data; it's referencing 1,048,576.

By referencing entire columns (unlike Alkey's solution, which went up to row 100 for all but the initial range passed to INDEX, which is the only one which should be an entire column reference), you are putting an incredible amount of extra resource into calculating even just a single one of those formulas.

Does your data really extend beyond row one million? Where did your 232 come from?

Regards

Perhaps the other formulas you were using were not array formulas.

Most functions can accept entire column references with no detriment to performance, including e.g. COUNTIF(S)/SUMIF(S), etc., though array formulas are forced to calculate over every single cell within the range passed, whether technically beyond the last-used cell in that range or not.

And by array formula I mean not just those which require CTRL+SHIFT+ENTER, but also the likes of SUMPRODUCT, LOOKUP and AGGREGATE (which actually are array formulas, required keystroke combination aside).

Regards

You're welcome!