Lack of understanding Index and match, and small formulas

I am having difficulty understanding how to combine the index, match and small functions in excel to search and return data in my spreadsheets. Does anyone have a good reference website that explains this? or the name of a book I can check out at the library?

Any help would be great!

One example:

Formula:

Please Login or Register  to view this content.

where n is number 1,2,3...
Value 1 is for minimum value in range, 2 is second minimum and so on.

Originally Posted by lreed

I am having difficulty understanding how to combine the index, match and small functions in excel ...

We are here to help you improve your understanding in those areas - do you have a particular problem you would like some help with?

Pete

Sorry it took so long to answer. I have a spreadsheet that I am trying to work with to creat a quick search for a department. I want to be able to enter a partial word in excel like "monitor" or "Printer", in cell A9 and have the spreadsheet return all possible parts, the severity of the repair and impact based on the corresponding tab. on the below lines. This would be the blue section of the attached spreadsheet on the "entry sheet". I know this is do-able, I have seen the formula before but am not understanding how to correctly put it together. I am pretty sure it is a combination of the index formula, with either match or small. If you look at the formula written in A10, you will see my attempt at the formula. If I can get it to work with the 1st vendor (blue section)I can figure out the formula for the second vendor (grey section) on the tab marked Entry sheet.

Is the attached file something like you are looking for? Let me know if you want to know how it works.

Hope this helps.

Pete

On the NCR and TOLT sheets I have used column E as a helper column with this formula in E16 of the NCR sheet:

=IF(ISNUMBER(SEARCH('Entry Sheet'!$C$9,A16)),MAX(E$15:E15)+1,"-")

This is looking to see if the word or phrase you have chosen in cell C9 of the Entry sheet is contained anywhere in cell A16 - if it is then a counter is incremented, giving a unique number for that record, but if not then a hyphen is returned, which helps to show that the formula is active (and you can see where the formula has been copied down to). The formula needs to be copied down beyond your data, in order to cope with more data being added. A similar formula is used in the TOLT sheet, though the row references are different as you have fewer header rows.

I've inserted two new columns A and B in the entry sheet for two other helper formulae - these columns can be hidden after the formulae have been set up if you want the sheet to look like it was before. This formula is in A10:

=IFERROR(MATCH(ROWS($1:1),NCR!E:E,0),"-")

This returns the row where the record number 1 is shown in column E of the NCR sheet. When the formula is copied down the term ROWS($1:1) becomes ROWS($1:2) (returning 2), then ROWS($1:3) (returning 3), and so on, which means that the formula is looking for record 1, then record 2, then record 3 etc. on subsequent rows, and returns the row in the NCR sheet where that record occurs. The formula in B10 is very similar, although it is looking for the record numbers in column E of the TOLT sheet. Again, a hyphen is returned when there is no match, indicating how far the formula has been copied down.

This formula is in C10 of the Entry sheet:

=IF(OR($A10="",$A10="-"),"",INDEX(NCR!A:A,$A10))

which gets the data from column A of the NCR sheet on the row where the record number 1 occurs in column E. When this formula is copied across to F10, it gets the data from columns B, C and D of the NCR sheet , in turn. A similar formula in G10 gets the data from column A of the TOLT sheet where the record number 1 occurs on that sheet, and the formula in H10 gets the data from column C. All these formulae can be copied down as far as you think you need them - I copied to row 50.

Then all you need to do is change the choice in cell G9 (e.g. to server) and then the display will automatically change.

Hope this helps.

Pete

This really does help, thank you so much for all your time and effort.