Matching and printing values from two excel columns in another column on certain condition

[meetvivek72]

Actually there is a problem in the attached excel sheet, here we are comparing two excel columns Number1 & Number2 . If the Number in both the columns i.e, Number 1 & Number2 is same then it will print the matching data from Sequence column i.e Column C in Final Sequence column i.e, Column D only. But in case if doesnt find the matching numbers, like in our example: Cell A5 is not matching with Cell B5, then it should go to cell B6 to find match & then print the matched sequence in red marked cel of column D.
Same is the scenario in case of cell A7 & B7. Here it will find the match in cell B9 and thus substitute the corresponding Sequence from cell C9 in cell D7.

[dilipandey]

Hi Vivek,

Try using below formula:-

{=INDEX($C$1:$C$9,MIN(IF($B$2:$B$9=$A2,ROW($C$2:$C$9),"")),1)}

See attached file:- task.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

[meetvivek72]

well its working fine in the sheet u have attached, but as we start stretching the formula downward, its not working properly....

[dilipandey]

ok..
check the references if they are moving down in order..
also are you applying formula with Ctrl + Shift + Enter key combination.. ?

Regards,
DILIPandey

<click on below 'star' if this helps>

[meetvivek72]

we r just stretching it only with mouse as it is not possible to stretch using ctrl+shift+enter keys..

[dilipandey]

I am not sure what is the issue you are facing :-

Do you :-

1) want to change the reference of the formula to downward or,
2) want to drag the formula itself to downward
??

would suggest you to apply the formula in any one cell with ctrl shift enter and then you can simply copy paste that formula downwards .. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>