I am having to use Excel to analyze big data at work (inefficient, I know, but at least I have 2 laptops). I need to run a lookup to pull order cost values into 240K rows of one sheet from roughly 1.5 mil rows of data that is separated on 3 other sheets. I also need to know the category of order I am dealing with - "Type A" or "Type B" (types mixed through 3 reference sheets). I am trying to choose the quickest (lol) method for how to handle this. With index/match being easier for me to use than vlookup (and taking less file space), I am using that for lookups:
Option 1: run a nested iferror/index/match lookup down 240K rows analyzing 1.5mil. records: (if not on sheet 1, pull from sheet 2, if not on sheet 2, pull from sheet 3). While pulled from someone else's solution, formula would look something like this (tested on small sample and does work):
=IFERROR(IFERROR(INDEX(ANGLES!$H$6:$H$85,MATCH([@StockCode],Table_Query_from_SysproCompanyA[StockCode],0)),INDEX(CHANNELS!$H$6:$H$85,MATCH([@StockCode],Table_Query_from_SysproCompanyA5[StockCode],0))),"")
Option 2: create 4 columns - 3 with index/match formulas referencing a different sheet [so 3x the formulas - each formula analyzing 500K rows instead of 1.5mil) and one with a helper formula. Each lookup line *should* return "#N/A" in 2 of the 3 columns and a sales value in another. Helper column will report the non/#N/A value via nested iferror (iferror column L, col. M, iferror col. M, col. N)
Please let me know your thoughts. I can work on my other laptop while whichever monstrosity I choose loads. Unfortunately, using Access/Tableau/something else is not an option for this project today and I do not have VBA knowledge.
Bookmarks