Using VLOOKUP + MATCH (HLOOKUP + MATCH, OFFSET + 2 MATCHes or INDEX + 2 MATCHes) to search a table in both axis. This post has been sitting for a while in my archive, waiting for me to get some time to polish and post.
Small file that will show how to do INDEX + MATCH formulas to get the abbreviation of the state based on its name or the name of the state based on its abbreviation Main formula to do that is: =INDEX(Abbr_States,MATCH(B4,States,0),1)
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)
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 […]
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 […]
