Making part of a statement iterative in Excel

I have a lookup that works for a single column:

=IFERROR(INDEX($C$2:$C$145,MATCH(A2,$D$2:$D$145,0)),"")

If A2 matches the range of values in D2 to D145, then output the corresponding C2 to C145 value in cell B2

This populates column B2 successfully.

How could I put that into a loop, so that the $D$2:$D$145 part iterates between column D and column GS? ($D$2:$D$145, $E$2:$E$145, $F$2:$F$145 etc)

Really appreciate any hints from anyone at this point.

Hi,

Are you asking if you can find the A2 value ANYWHERE in D2:GS145 and if so return the row number for use in the INDEX() function?

Hi,

Are you saying that if the lookup_value is not found in column D, then columns E and F should then be searched, in that order?

Regards

I'll use a single row as an explanation here:
Column A has 21,209 items in it. I need to identify where each of those matches anywhere in this range of values, so that I can create a single 'results column' that can be cut and pasted back into a master spreadsheet.
A2 in this example will only ever match once against a value in the range D2 to GS145. Once you have a match, no need to continue searching.
C2 to C145 is the results I need to concatenate, based on the pattern match
If A2 was found in L143, then C143 should be copied to B2
If the cell in D2 to GS145 is blank, it can be ignored if that makes it easier.

Hi,

I was sort of with you until you mentioned concatenation and pattern match and then you lost me completely.

On the basis that a picture is worth a thousand words please upload an example workbook, (we don't need 21000 rows, a handful will do), clearly showing the data and results you expect. Manually enter the results and a note which clearly says in a narrative form how you've arrived at the results.

"I was sort of with you until you mentioned concatenation and pattern match and then you lost me completely."

Me too!

Hi,

One way. In B2 and copy down:

=INDEX($C$2:$C$15,SUMPRODUCT(($D$2:$P$15=A2)*(ROW($D$2:$P$15)-MIN(ROW($D$2:$P$15))+1)))

Regards

Ah, yes. Apologies.

=IF(SUMPRODUCT(--($D$2:$P$15=A2))=0,"",INDEX($C$2:$C$15,SUMPRODUCT(($D$2:$P$15=A2)*(ROW($D$2:$P$15)-MIN(ROW($D$2:$P$15))+1))))

Regards

I was initially impressed, then a little confused, by the first formula. It seemed to work but then, as I looked at it, came to the same conclusion as the OP.

Still impressed, especially with the modified solution, as I'm sure I've seen similar questions in the past and had no idea how to approach them.

I've taken the liberty of putting the formula into the sample workbook and adding some Conditional Formatting. This highlights the matches so serves as a cross check.

Regards, TMS

@TMShucks

Glad you liked the solution!

@Both: You're welcome.

@asparak: Thanks for the rep.