Repeat Lookup value for reference

[Jonathan9]

Hi, I have a reference appears multiple times. Following that reference there is always a value. So if the same reference goes on for example 3 rows I want to look at the next column and find a specific value and if it finds it then repeat it for all those 3 rows.

And if that specific value doesn't exist then leave the cell blank.

Please see attached workbook.

Thanks!

[JeteMc]

If I understand the query correctly and you want the referenced value to be placed in column D try the following array entered formula*:

Please Login or Register  to view this content.

*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
If on the other hand you want to replace the existing values in column C, I imagine that a VBA solution will be needed.

[Jonathan9]

Hi!

Thanks a lot. This works. It would be interesting to understand it. I get the Index and Large function - but the other ones like * column B I don't really get.

[JeteMc]

It would be interesting to understand it. I get the Index and Large function - but the other ones like * column B I don't really get.

A way to study what a formula is doing is to use the Evaluate Formula function on the Formula tab. If you select D4 and run the EF function it shows that of the first 100 rows "20150902_AT_REA_RN_NONTOP" is found in rows 4, 5, and 6. Since 6 is the largest value it will then compare the value in C6 to that value in I5. I hope that helps, let me know if you have any questions.

[FlameRetired]

An afterthought. Does not require array entering.

Please Login or Register  to view this content.