-
Sounds like your run of the mill VLOOKUP. However this has limitations (the lookup needs to be in asending or decending order). Otherwise you can use a nested INDEX MATCH formula. This has the advantage of not needing an ordered list but takes a bit of getting your head around to implement. Try googling...
-
I teach data analysis using pivot tables in a short course and we usually need to use lookup functions such as VLOOKUP in order to import fields from different data tables into another one before building a pivot table to get the desired analysis results. Sometimes, VLOOKUP is not an appropriate choice...
-
Hi Guys, Good day to you! I am trying to make a training plan and record in one workbook. In training plan i have to put numbers, which represent for key training description and the other sheet - the training record i have to input the actual dates of training they undergone. So, when dates will be...
-
I am currently putting together a database of timesheets which contain Name, date, hours (plus others, but these are main ones). I need to lookup into a standing data table two criteria, the name and the date. The standing data consists of name, date of rate change and new rate. Therefore I need to find...
-
Hi Steve, Rather than use VBA, I've developed a worksheet only solution, using dynamic named ranges, LINEST linear regression function, datatables, index/match to pick out the best results, and finally solving the simultaneous equations and plotting!! Sorry I don't have time to explain the whole...
-
To simplyfie bentleybob's suggestion you can have a look at this add-in: http://www.mrexcel.com/chal/pareto.zip All of the formulas in B are =RAND() functions. Click the checkbox to recalc, and a whole series of dynamic range names cause the chart to redisplay sorted values. It also make it possible...
-
Good afternoon everyone, it's me again. Well, I have another question. This time, I have an answer myself but I would like to hear some recommendations, because I'm not sure I solved this problem in the most elegant way :-) So. The setting is the following. We compare the average sales of last...
-
Dear forum users, I am working on a tool which graphically shows the stock coverage in weeks for all products we have, per product. We have pre-defined max coverage and min coverage. On this picture you can see in red the max, in blue the min and in green the actual (projected) coverage. http://docs...
-
This ties a little to zfraile's post on faster lookups but most definatlely applies ONLY to unsorted lists. In this environment specifying a sorted list is not an option. A lot of our workbooks use Vlookups extensivley but the recalculation times are starting to suffer. So I started combining MATCH...
-
This workbook has a sheet of each type available in Excel. if you click on the 'Create Index' button, it adds a worksheet with the worksheet name, type and, if possible, a hyperlink to that sheet. It just a file by way of VBA demo. (Alt+F11 to look at code)