index & small

  1. blackorchids2002

    Sort a 2 array of data

    Hi Masters, I need your help again. I was trying to use an index formula to sort the 2 sets of data from a smallest value. I cannot get it sorted how I want the data should look like in my excel. =IFERROR(INDEX(A$1:A$44,SMALL(IF($B$1:$B$44<>"",ROW($2:$44)-ROW($2:$2)+1),ROW($A2))),"") 1ST SET...
  2. I

    how to get data from all 4 sheets???

    how to get data from 4 sheets in another 5th sheet. right now i am getting data from 1 sheet as this formula =IFERROR(INDEX(PAINT!$E$7:$J$406,PAINT!$D128,COLUMNS($B$16:F137)), " ") but how to get data from all 4 sheets???
  3. L

    Formula to find a value by going right, down, and then left.

    I am looking for a formula that will find the value in cell B1 in the row B5:I5. Then go down from that date to the first non-blank cell. Then go left and return the corresponding value/name in column A6:A12 into cell F1. Thanks in advance. I have been racking my head trying to figure this out...
  4. dreen

    Vlookup & Offset #Value Error

    I have two workbooks, one where I am doing the Vlookup and the other contains the table I need to retrieve the information from. I am trying to basically Vlookup the table found in the other workbook '[Database_IRR 200-2S.xlsm]Changes'! below row 3 as sometimes the lookup retrieves information...
  5. dreen

    Dragging Index Formula down rows and changing the return column

    I have a formula that I am using (after you paste it press "Enter+Ctrl+Shift" to run it as it has an array inside of it): =INDEX('[Database_IRR 200-2S.xlsm]HE 171'!$F:$F,SMALL(IF('Operation base Q'!$H$5='[Database_IRR 200-2S.xlsm]HE 171'!$A:$A,ROW('[Database_IRR 200-2S.xlsm]HE...
  6. C

    Extract Values that meet criteria

    Hello All, I am trying to extract some data from one sheet into another based on two different criteria. I need to select the data from the last six months and where column C = 1. So far I have been using an Index and small function. I have been able to extract some data, but not the correct...
  7. G

    Find match for nth value with multiple criteria

    I have this formula, I feel I am making a very small mistake, but I keep receiving the #N/A error. I've been working on trying to find a fix to it for about 2 days now, any help would be greatly appreciated. 1 2...
  8. D

    creating BINs by altering variables

    Seems simple enough but i have been unscuccesful DATA: columns NodeName, Weight A1, 800 A2, 800.1 A3,820.9 A4,815.3 A5,801.6 A6,801.6 A7,815.3 you get the idea. very simple data, except i have thousands. I've tried creating histograms and that works fairly well but you can't really see the...
  9. A

    Lookup and return multiple values sorted in a custom order

    I want to use the function in this link ( but the only difference is that I want mine in descending order not ascending order. How do I modify this function to do that? This is the function...
  10. H

    Extract Rows/Cells from Master Table to Sorted Tables based on Suffix of Text.

    Hello fellow excellors, I have a number of assets that contain very specific suffixes, for example: XRD05_CV XRD06_CV XRD07_CV XRD08_CV_I XRD08_HB XRD08_WS XRD09_CV These assets are listed in a master table. as you can see, there are different suffixes such as _CV, _CV_I, _HB and _WS. Now I...
  11. A

    Populate empty table with data from other tables based on dropdown list selection

    I have several tables in one tab which I have named individually. In another tab I have a table that I wish to populate with the same information of the selected table based on a drop down selection. All tables are the same size. The dropdown is also a named range. The list in the dropdown is...
  12. J

    return multiple results, but then display the cell reference of each result

    Hi Everyone, Would you be able to help me create a formula that will allow me to return multiple results, but then display the cell reference of each result I tried to use INDEX MATCH but it will only display the first match that it finds then return the cell reference of the match, this is the...
  13. R

    extracting a list with 2 criteria

    i am trying to add another criteria to this formula so that once i copy the formula down it will generate a list. it works with one criteria but i need it to use two. =IFERROR(INDEX($A:$A,SMALL(IF($D:$D=O$3,ROW($D:$D)-ROW($D$1)+1),ROWS($D$1:$D1))),"") how would i go about adding the second...
  14. R

    Count "1" in a serial in a row

    Hi A have a row with 0 and 1 and they comes in a serial. A wants a formula to calculate the largest/ second largest serial of 1's in a row. I wants to variate were to start and stop, sometimes I have to start counting in a middle of a serial of 1's 1111111110000000111111000000000001111111111...
  15. L

    Index Small If Row Horizontally with no Duplicates

    Hi there, Would really appreciate any help with this! I have 2 sheets: Combined Data and VGBB Summary. The snapshot below is VGBB summary. What I need to do is bring over all of the prices associated with each category (e.g. Accessories) to the right of the category. My issue is that there...
  16. L

    Modify Index Small Row Formula to summarize all Rows

    Looking for help in the following: Trying to move data in multiple rows in tab "Colors" to one row on "Summary" tab. Colors tab looks like this: A B C D 1 Red 1 2 2 Red 10 11 15 3 Red 23 150 256 4 Blue 5 6...
  17. R

    time recording multiple options

    Please help I have a spreadsheet that manages queries on a daily basis, each entry is time managed by a userform (ie timer starts when form opened and stops when closed) the start, stop and elapsed time get input on the log. This log is used by several people daily. the formula i have at the...

Some videos you may like

This Week's Hot Topics