Zipcode-State search

Enter a zip code, and Excel will tell you in which state it is It also has a list of all zip codes for all states and a technique (INDEX and MATCH functions) to get the state from a given zip code. Simple file to help learning INDEX and MATCH The formula used is: =INDEX(Data!$D:$D,MATCH(TEXT(Search!C4,”00000″),Data!$C:$C,0),1)

Dynamic selection list

Using Index and CountA Formulas, Names and Forms > ListBox to have a list with connected formulas Can also be used as form to read from a large table in another sheet   After creating two names, one is “Months” having: =OFFSET(Sheet1!$B$5,0,0,COUNTA(Sheet1!$B:$B)-2,1) And the other one is “Month_All” having: =OFFSET(Sheet1!$B$5,0,0,COUNTA(Sheet1!$B:$B)-2,3) Draw a listbox using “Developer […]

Sort list dynamically (functions)

Sorting a list automatically using formulas, with no need to press the sort command Also if the source table is changed, the destination table will do also. What you need is basically two formulas, one for the sort-by column to list items by order. Use SMALL to sort ascending, or LARGE to sort descending, then […]