Formula required to copy values in a Matrix as step down according to serial number

[sameer79]

Dear All,

I am using Excel 2013 and have a data set as shown in the sample attached

I have the Before Data

I would like to generate the output shown under the After

I would like a formula in Cell H4 which I will drag across and down to get the required output according to the S.No

Any help would be appreciated

TIA

[jme1013]

What are the conditions that cause each name to be populated?

From what I see in your example, if the S.no and Item # match it = John

if they are off by one that = Mary

off by two that = Sam, and so on...

Am I reading this correctly?

[menem]

Please try.

H4

Please Login or Register  to view this content.

Regards.

[sameer79]

Thanks Menem
Super
Post marked Solved, Rep Added

[sameer79]

Dear Menem
A small issue
Can you modify the formula to look for Zero under the S No

Please see the sample attached
Thanks in advance

[menem]

Add more check zero before old formula.


Regards.

[Bo_Ry]

Please try at H4
=IF($G5,IFERROR(INDEX($A$4:$E$4,COLUMNS($H5:H5)-$G5+1),""),"")

[sameer79]

Dear Menem
Thanks a lot
Post Marked Solved

[sameer79]

Dear Ray
Thanks a lot for your version too
It works perfect
Rep Added and Post marked SOLVED