Complex index formula not working after updating source data

[SteveD562]

I Spend a good amount of free time on a flight simulator and have created an excel sheet that using data will give me a random valid destination to fly to next. For valid I mean, I have the entire route database of 9,079 valid routes. The formula would look to see my arrival airport code, then see what destinations are available from that arrival code, then giving one of the destinations at random. Someone else built the formula for me years ago and it worked flawlessly up until today when I had to rebuild the database, but did not change where the columns already where on the "Routes" tab, only increasing the number of rows with data to be considered which I then adjusted the formula and now SOME, but not all give a result while most show #NUM! error.

Here is the formula: INDEX(ROUTES!$H$2:$H$9081,SMALL(IF(ROUTES!$F$2:$F$9081=C13,ROW(ROUTES!$H$2:$H$9081)-ROW(ROUTES!$H$2)+1),RANDBETWEEN(1,COUNTIFS(ROUTES!$F$2:$F$9081,C13))))

So basically, H Column is the arrival airport codes in the database, F Column is the departure airport codes in the database, and in this formula C13 would be the actual departure airport. So if it works correctly, it would function the same as if I were to go to the Routes tab, filter departure airport code to the actual departure airport code, thereby showing all the available arrival airports and then randomly picking one of those.

Also, for the few rows that are actually showing an airport code and not the #NUM! error, not all of them are correct. For example, I have that I will be departing from airport code ZYJM. Where the formula is that should be giving me a random valid arrival, it is returning a result of arriving at airport code ENGM. If I go to the Routes tab (the database) and filter to show only routes departing from airport code ZYJM, the 1 and only option is airport code ZSPD. So the formula should never see ENGM as an option because that is not valid. Also, no matter how much I change other cells (like pressing F2 in an empty cell and then enter) that ENGM airport code will always show in the same row and never change to anything else, error or other airport code, despite the formula being active in that cell.

So clearly something is wrong with this formula now after updating the routes but I cannot find where the error is or why it is there. I will be happy to post screenshots of the workbook if needed, but uploading it would be difficult as it is quite large (lots of formulas calculating data based off of other data entered over many tabs).

Walking thru the formula in Evaluate Formula, it does in fact do as it should. I can see it finding the count of routes (and I verified that count), asking to give a random number between 1 and the number of routes, selecting the random number, but then will go to the #NUM! error after it selects the random number. Seems to me the issue may be in the final steps? Why is it giving an error instead of the value it is finding?

 

[mole999]

anything that has gone in as text ?

 

[jasonb75]

Best guess would be that you only pressed enter after editing the formula. It is an an array formula, so would need to be confirmed with Shift Ctrl and Enter, not just Enter.

Select the top cell with the formula, press f2, then press Enter while holding down Shift and Ctrl. If you have done it correctly, when you look at the formula bar at the top of excel, you will see that the formula is now enclosed in curly brackets.

If you're using excel 2010 (or newer) then this alternative formula will not need to be array confirmed.

=INDEX(ROUTES!$H:$H,AGGREGATE(15,6,ROW(ROUTES!$H$2:$H$9081)/(ROUTES!$F$2:$F$9081=C13),RANDBETWEEN(1,COUNTIFS(ROUTES!$F$2:$F$9081,C13))))

 

[SteveD562]

I found the issue. I was able to covert the error to #N/A error after doing Ctrl+Shift+Enter (still had to do that with Excel 2019) and then using the Evaluate Formula and looking at every False and True in the Small formula, I found it was finding some #N/A. So I went back to the database and realized that I had changed some of the codes in the raw data (what the database looks up its info from) but had not made the changes in the database (the route is the only thing not a formula in the database as it is copy/paste from the raw data).

Anyway, after copy/past the routes from Raw to Database, the entire thing works properly now. Amazing how 1 small change can cause random errors lol.