Multiple lookup with non-adjacent columns

This array formula** entered in A2:

=IFERROR(INDEX(Sheet1!R:R,SMALL(IF(ISNUMBER(MATCH(Sheet1!F$2:F$22,A$1:E$1,0)),ROW(Sheet1!F$2:F$22)),ROWS(A$2:A2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

Originally Posted by wator89

=IFERROR(INDEX(Sheet1!$R:$R, SMALL(IF($A$1=Sheet1!$F:$F, ROW(Sheet1!$F:$F)-ROW(Sheet1!$F$1)+1), ROW(1:1) )),"" )

You should avoid using entire columns as range references in array formulas. Use smaller specific ranges.

Using the ROW function leaves the formula vulnerable to new row insertions. Insert a new row 1 and see what happens to the results. Use the ROWS function instead. It can account for new row insertions.

As an alternative here's a non-array (ordinary) formula that does the same thing.

=IFERROR(INDEX(Sheet1!R:R,SMALL(INDEX((Sheet1!$F$2:$F$22<>Sheet2!$A$1:$E$1)*10^10+ROW(Sheet1!$F$2:$F$22),0),ROW(1:1))),"")

... or taking Tony's point:

=IFERROR(INDEX(Sheet1!R:R,SMALL(INDEX((Sheet1!$F$2:$F$22<>Sheet2!$A$1:$E$1)*10^10+ROW(Sheet1!$F$2:$F$22),0),ROWS($1:1))),"")

Originally Posted by Glenn Kennedy

As an alternative here's a non-array (ordinary) formula that does the same thing.

The array formula is slightly faster to calculate.

Also, ROW(1:1) leaves the formula vulnerable to new row insertions.

Point 1, perhaps so, but non-array formulae are easier to keep working (accidental unsetting of the array). In general, most folk here seem to agree that non-array formulae are preferred, wherever possible.

Point 2, agreed & see my comment at Post 6.

Originally Posted by Glenn Kennedy

In general, most folk here seem to agree that non-array formulae are preferred, wherever possible.

I think that most folks would prefer the BEST solution to their task. Going to "extremes" to avoid an array formula is not a best solution IMHO.

Not using array formulas is like a carpenter showing up on a job without a hammer!

Sure, they have other tools that will get the job done but it's just not the same.

Agreed with Tony. I think, as Experts/Gurus on a well-visited Excel Forum, we should be doing all we can to dispel this unfortunately widespread "fear" of array formulas.

Even for new users, apart from the ever-so-mildly challenging problem of having to remember to (re)commit the formula with CSE, there is no other reason that I can think of which should mean that we promote non-array formulas over the equivalent array version, even more so since such practices often result in constructions which are not only more difficult to understand (try explaining the use of INDEX with zero parameter(s) as a substitute for CSE to a new user!) but also less efficient.

Regards

It would be much better to use a named range!!

It's almost always better to use dynamic ranges.

Failing that, choosing a suitably large upper bound on the end row reference is still far, far preferable to using entire column references when the formula in question is an array formula. For example, if you only actually have data extending to row 1000, just one iteration of an array formula using entire column references would force Excel to calculate more than a million additional, unnecessary cells.

Regards

Originally Posted by wator89

Maybe it would be a good idea to use dynamic ranges?

Yes.

We're all in agreement on that!

Do you need assistance on how to do that?