I'm a bit stuck and could use some help.
I am trying to keep a list in excel updated dynamically, based on conditions in other cells. Something along the lines of (IF both cell values in A1 and A2 MATCH the cell values in A3 and A4, return A5). I'd ideally like to use just formulas and no scripts, but not sure if it's possible.
Here's a scenario. Gina (A3) has a shopping basket with 6 items in it (B3:B8): Lemon, Toothpaste, Brownie, Hairbrush, Grapes, Sandwich –
Gina and her friends like to trade things with each other, and when they do they make a record of each trade (D3:F5) –
record of trades
What I'm trying to do is figure out how to update Gina's shopping basket every time she records a new trade.
And in addition it needs to allow for trading the same item twice (for different items on each occasion) accounting for only the most recent entry traded for that item – in this case she is receiving an orange in her basket –
in this case she is receiving an orange in her basket not, not a cupcake
I've tried various combinations of IF, AND, MATCH and INDEX with duplicate second column, or getting stuck in circular dependencies, and I can't get to the bottom of it.
Here's a link to the worksheet: https://docs.google.com/spreadsheets/d/17J-lX2V1Zs-K7WmsfruqcEJtmElM5rCQTeCLFh8FX1U/edit?usp=sharing
If anyone has any ideas on how to solve this, I would be hugely grateful!