Paste across and color

[poikl]

Hi,Thank you for helping me in the past.
I have a formula in Cel# J2 entered as ARRAY, (which I adapted from MREXCEL replies to others):
=IFERROR(INDEX('[Inx.xls]All Inxs'!$L$2:$L$9839,SMALL(IF('[Inx.xls]All Inxs'!$B$2:$B$9839=$A2,ROW('[Inx.xls]All Inxs'!$I$2:$I$9839)-ROW('[Inx.xls]All Inxs'!$I$2)+1),COLUMNS($J2:J2))),"")
1) Can you please correct it so it can easily be pasted across to adjacent Columns J, K, L, M, N (without having to change the formula every time?
2) And more importantly, to COLOR Green, if the Left 3 letters (of the result in Col J,K,L,M,N) match the Left 3 Letters in adjacent ColG so it can be identified as a match?
Thanks again

 

[poikl]

PLEASE-Re: Paste across and color

I'd be so grateful if someone can please try helping me with my urgent work demand

 

[Marcelo Branco]

Re: PLEASE-Re: Paste across and color

I have not tested your formula, but it looks perfect to me and can be copied across to yield multiple results, since:
in J2 the part COLUMNS($J2:J2) is equal to 1 and the formula returns the smallest row that meets the condition;
in K2 this part becomes COLUMNS($J2:K2) that is equal to 2 and the formula returns the second smallest row
and so on

What is your problem?

Don't understand the question about color, but i think you can use conditional formatting.

A small data sample along with expected results would be helpful.

M.

 

[poikl]

Marcelo thank you for reviewing my urgent problem. I want to avoid changing the code from "J" to "K" to "L" etc every time that I need to paste across.

 

[Marcelo Branco]

Marcelo thank you for reviewing my urgent problem. I want to avoid changing the code from "J" to "K" to "L" etc every time that I need to paste across.

I'm confused...
I think you don't need to change anything.
Just copy/drag the formula to the right and everything should work fine.When copied the formula automatically changes Columns($J2:J2) to Columns($J2:K2); to Columns($J2:L2)....
Have you tried?

M.

 

[poikl]

I'm confused...
I think you don't need to change anything.
Just copy/drag the formula to the right and everything should work fine.When copied the formula automatically changes Columns($J2:J2) to Columns($J2:K2); to Columns($J2:L2)....
Have you tried?

M.

Marcelo, Thank you very much for helping me I didn't realize that it changed the column automatically.
Can you please help me with coloring if the Left 3 letters (of the result in Col J,K,L,M,N) match the Left 3 Letters in adjacent ColG so it can be identified as a match?

 

[Marcelo Branco]

Select J2:N2 being J2 the active cell (the one not shaded after the selection)
Home > Conditional Formatting > New rule > Use a formula to determine which cells to format
insert this formula
=LEFT($G2,3)=LEFT(J2,3)
Format button
pick the format you want

M.

 

[poikl]

Select J2:N2 being J2 the active cell (the one not shaded after the selection)
Home > Conditional Formatting > New rule > Use a formula to determine which cells to format
insert this formula
=LEFT($G2,3)=LEFT(J2,3)
Format button
pick the format you want

M.

Thank you Marcelo again for helping me with this. It worked perfect.

 

[Marcelo Branco]

You are welcome. Glad to help.

M.