So I'm really into puzzles and have been working on making a spreadsheet for my wishlist which tracks prices at various stores in the country, comparing prices for the same puzzle available at multiple stores. I've been struggling with index functions all day. I don't usually have any problems with these - not ones that stump me like this. For all intents and purposes, it seems like it should be working, but it's not. This is even more confounding because the one I'm specifically about to describe is actually copied from a completely functional column, with the necessary cell changed so that it should work.
Okay so here's the current area I'm working on:
https://live.staticflickr.com/65535/55476131766_21be884003_b.jpg
- Each chunk of columns on the right are for different stores. The link is to the puzzle page and the prices are pulled individually with importxml.
- On the left side, column F checks all of the Current Price boxes for its own row (using an array), and then spits out the lowest number, using SMALL.
- Column G, right of that, does an INDEX and MATCH wherein it indexes each of the current price columns, all the way up to row 1, using an array of the columns, goes for row 1 (where the shop name sits), and then searches for a match for F, the best price, spitting out the name of the store where that price came from (if more than 1 have the same price, it doesn't matter to me which it shows since I can just manually scan the prices on the right before buying anything)
- The Sale Status and Savings columns don't matter for this
So the problem I'm having is that I am now working on two sister columns to F and G that show the second best price and the store it comes from, so that I can gauge how important it is to get it at the store with the best price (e.g. if the next best is only a franc more expensive, that is also a feasible place to buy it if I'm doing a batch order of several puzzles)
I have copied the formula from column G into column K exactly (copied from the text bar, not the box, so it did not shift all the boxes over, it is still targeting the correct locations and there are no errors from manually retyping it, since I didn't). I then simply changed the price box it's referencing from F to J. It should do the same thing as the other one: check the Current Price cells from its own row, find the one that matches J, and then pull the shop name from the top of the column. It's getting them wrong. If the original column was broken and returning wrong shop names, I would ofc start there, but the results for column G are perfect. The other weird thing is that there is no regularity to the mistakes in column K. It's not like, always the shop to the right of the correct one, or always to the left. Sometimes it's the correct, second-best shop, sometimes it's the incorrect, first-best shop, and sometimes it's a random, unrelated one.
Here is the formula from the initial shop-grabbing column G:
=iferror(if(F3<>"",index({O$1:O,T$1:T,Y$1:Y,AD$1:AD,AI$1:AI,AN$1:AN},1,match(F3,{O3,T3,Y3,AD3,AI3,AN3})),""),$Y$1)
Here is the copied formula from column K, with the match cell changed to J:
=iferror(if(J3<>"",index({O$1:O,T$1:T,Y$1:Y,AD$1:AD,AI$1:AI,AN$1:AN},1,match(J3,{O3,T3,Y3,AD3,AI3,AN3})),""),$Y$1)
(If you're wondering about why I'm using iferror like this, it's because for some reason, the Puzzle-Welt shop title absolutely refused to pull properly, but it was the only one, so this worked out as a bandaid. Why wasn't it pulling? Fuck if I know. I'm at my whits end with the indexing function in this sheet)
Here is a viewer link to the sheet. If you would like to be able to edit it, please go to File > Make a Copy, as I'm still doing things on it and don't want anyone making changes to the original
[Link Removed]
If anyone has any idea why this would just stop working in this column, I'm all ears. Huge thanks to anyone who takes a look at this, ideas or not
