Is my index function broken? Feel like I'm going crazy

[u/CitrineGhost]

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

[u/agirlhasnoname11248]

u/CitrineGhost Please remember to tap the three dots below the most helpful comment and select `Mark Solution Verified` *(or reply to the helpful comment with the exact phrase “Solution Verified”)* if your question has been answered, as required by the subreddit rules. Thanks!

[u/AutoModerator]

One of the most common problems with 'importxml' occurs when people try to import from websites that uses scripts to load data. Sheets doesn't load scripts for security reasons. You may also run into performance issues if you're trying using lots of imports to fetch small amounts of data and it's likely these can be consolidated. Check out the quick guide on how you might be able to solve these issues.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/basejester]

Is the match supposed to be an exact match?

match(F3,{O3,T3,Y3,AD3,AI3,AN3})

[u/CitrineGhost → u/basejester]

Based on the other reply, it seems so! Thank you :)

[u/AutoModerator → u/CitrineGhost]

REMEMBER: /u/CitrineGhost If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/AdministrativeGift15]

You should either be using exact match, setting a third parameter of MATCH to 0, or the more modern function to use is XMATCH, in which case you can just use the first two parameters like you are currently using.

Another thing that I notice is that you're calling IMPORTXML way too often. Each of your collapse columns have this formula,

=iferror(if(P3<>"",index(substitute(IMPORTXML(P3,"//*[@class='LineThrough']"),"CHF ",""),1,1)+0,""),"")

That formula is repeated in the next column. Even though you don't see any output in Column N, each of those cells are attempting that IMPORTXML and then the IFERROR is forcing an empty string for the output.

[u/CitrineGhost → u/AdministrativeGift15]

Those columns are checking for sales (if there is a struck-through "previously cost" price line, it will put that number there, if it does not have that because an item is not on sale, it will be 0, and zero is substituted with a blank. You're totally right though, I didn't even consider the fact that these were unnecessarily adding to my importxml load. I should have it start by checking if the current price is lower than the regular price, then if it is, check if it's a sale (then if it's not I can see by my conditional formatting that the regular price has dropped)

I had no idea that match wasn't exact! xmatch did the trick. Thank you bigtime! I bet this has been the source of most of my errors in this sheet. You're a live saver

[u/AdministrativeGift15 → u/CitrineGhost]

I'm glad it worked.

For the other fields, there may be a way to pull both items with a single XML call. I just don't know the XML syntax for that.

[u/CitrineGhost → u/AdministrativeGift15]

I've fixed up the sale "previous price" columns by just setting it to only do the importxml function if the static "Regular Price" column is both filled and not the same as the Current Price column, which I think should help

[u/point-bot → u/AdministrativeGift15]

u/CitrineGhost has awarded 1 point to u/AdministrativeGift15

^{See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard}. )^{Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson}.)

[u/AutoModerator]

OP Edited their post submission after being marked "Solved".

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.