Adding new value from a separate field based on a variable in a separate field

[masakowitz]

I need to be able to look in a given column for a variable - find the corresponding ID (in another column field) and copy that ID value to a new column field until the variable changes. Once the variable changes I need to re-capture a new corresponding ID and repeat. In the file attached is a sample of the data I am working with and what the data looks like before - and what I would like it to look like after this operation.

In column E (E2 is a value). I want to find the corresponding ID in B2 and copy that to A2 through A22. At E23 the value changes - now I need to reset to capture B23 to copy to A23 through A26 - and repeat each time the value in column E changes. Ideally I would like to do this in a formula/function as I am not good with VBA coding.

Can someone help?

Mark
View attachment 25661

 

[Faseeh]

Hi Mark This is somewhat simpler solution:

In Cell A4 of your sheet put:

=E4

In Cell A5 downwards you put:

=IF(E5="",A4,E5)

..and drag down the second formula. Hope that helps.

..see also the attached sheet.

 

[masakowitz]

Hi Mark This is somewhat simpler solution:

In Cell A4 of your sheet put:

=E4

In Cell A5 downwards you put:

=IF(E5="",A4,E5)

..and drag down the second formula. Hope that helps.

..see also the attached sheet.

Thank you

I have also been researching after posting this question and came up with this solution too: =IF(ISBLANK(E3),A2,B3).

Thanks again for the reply