Pre populate cell to left and right to match what was there last time that word was used

[RFGC]

Is there a way to type a word into a cell in the B column. Then for the A and C cell either side to pre populate based on what you wrote in the A and C column last time that word was put in the B column.

I hope I have written that in not too much of a confusing way.

Any help gratefully received.

Thank you

[PaulM100]

If you already have a set of values manually added above, you can use something like =IFERROR(LOOKUP(2,1/($B$1:B4=B5),A1:A4),"")
In my formula I already have values assigned to different words in A1:C4. When I added the same word in B5, it populated the cell with the last value found above.
The ifferror will exclude errors, keeping the cell blank until you enter a value. Change the B A range with C for C column

[RFGC]

Sorry, I'm a bit of a novice with major automatic formulas. Where do i type that formula that you have mentioned above? I want to type into a b column cell. Then for it to pre populate the A and c cell either side according to what i type in the B cell. To match previous mentions of that same b cell mention.

[PaulM100]

An example:

[RFGC]

Thank you, that works perfectly on your example. But where do i put that formula for it to do it on mine. If I paste it into the multiple cells in my A column, it doesnt do anything. Sorry to be a pain.

[RFGC]

As a working example, I have text up to, and including line 52. So line 53 is where the substitutions would apply.

[RFGC]

So would i have this in A53...

=IFERROR(LOOKUP(2,1/($B$1:B52=B53),$A$1:A53),"")

[PaulM100]

Yeah, that's correct. And whenever you add data in B53 or any other cell below that is found in A1:C52, it will get updated.

[RFGC]

Thanks, I think ive sorted the A column now.

Does it only work for those you've written before you started the auto fill lines. I.e if I got to line 100 and added a new word that wasnt used before and filled in A and C accordingly. If i wrote that word in a cell in B200. Would it auto fill A200 and C200. Or not because that wasnt in those first 52 lines i did myself? Thank you again for all you help.

[PaulM100]

Yes, but only if you drag the formula to line 200 and replace the formulas on line 100 with manual entries. Kind of messy to keep both manual values and formulas, but it should work

[RFGC]

Thanks Paul. You've been amazingly helpful. Much appreciated.