Skip to content

Excel Functions

Excel f(x)s = Excel Functions

  • Shapes with dynamic output Excel User tips
  • Return 12 values in one number AND
  • Filter. Google Sheets function Google Sheets
  • Week number to Sunday date – Weeknum reverse DATE
  • Multiple lines in cell using functions CHAR
  • Count cells with condition – multiple conditions COUNTIFS
  • 0% interest promotion Basic Math
  • Rotate table using 1 formula ADDRESS

Category: INDEX

Searching table in 2 dimensions.

Posted on July 26, 2022 By ANmar
Searching table in 2 dimensions.

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.

Read More “Searching table in 2 dimensions.” »

Formulas - combined functions, HLOOKUP, INDEX, Lookup and References, MATCH, Standard functions, VLOOKUP

State Abbr with Index+Match+Validation

Posted on February 14, 2012 By ANmar No Comments on State Abbr with Index+Match+Validation
State Abbr with Index+Match+Validation

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)

Read More “State Abbr with Index+Match+Validation” »

Data Validation, Formulas - combined functions, INDEX, Lookup and References, MATCH, Standard functions

Zipcode-State search

Posted on February 12, 2012 By ANmar No Comments on Zipcode-State search
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)

Read More “Zipcode-State search” »

Formulas - combined functions, INDEX, Lookup and References, MATCH, Standard functions, TEXT, Texts and Strings

Dynamic selection list

Posted on February 7, 2012 By ANmar No Comments on Dynamic selection list
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 toolbar”, then make its input list as “Months” name we did above, then use these formulas:

Read More “Dynamic selection list” »

ActiveX controls, COUNTA, Formulas - combined functions, INDEX, Lookup and References, Names, Standard functions

Sort list dynamically (functions)

Posted on February 5, 2012 By ANmar No Comments on Sort list dynamically (functions)
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…

Read More “Sort list dynamically (functions)” »

COLUMN, Formulas - combined functions, INDEX, LARGE, Lookup and References, MATCH, Math and Trig, ROW, SMALL, Standard functions

Recent Posts

  • Stale values (Scratched functions)
  • Middle name + 1st name
  • LastSunday, LastSaturday
  • Paste Special Percentage
  • Jan8 date format

Archives

Categories

  • Array formula (1)
  • Formulas – combined functions (58)
  • Google Sheets (2)
  • Non-functions (36)
    • ActiveX controls (2)
    • Conditional Formatting (7)
    • Data Validation (7)
    • Excel User tips (19)
    • Format Cells (6)
    • Graphics (4)
    • Names (3)
  • Standard functions (82)
    • Basic Math (18)
    • Date and Time (16)
      • DATE (9)
      • Hour (1)
      • MONTH (7)
      • NETWORKDAYS (2)
      • TODAY (7)
      • WEEKDAY (8)
      • WEEKNUM (1)
      • YEAR (8)
    • Engineering (1)
      • CONVERT (1)
    • Information (6)
      • CELL (4)
      • ISERROR (1)
      • N (1)
    • Logical (30)
      • AND (5)
      • IF (24)
      • IFERROR (6)
      • IFNA (1)
      • IFS (1)
      • ISERROR (1)
      • OR (2)
    • Lookup and References (36)
      • ADDRESS (4)
      • CHOOSE (3)
      • COLUMN (7)
      • FORMULATEXT (1)
      • HLOOKUP (1)
      • HYPERLINK (2)
      • INDEX (5)
      • INDIRECT (6)
      • MATCH (12)
      • OFFSET (15)
      • ROW (11)
      • VLOOKUP (5)
    • Math and Trig (24)
      • ABS (1)
      • CEILING (1)
      • COUNT (1)
      • COUNTA (4)
      • INT (9)
      • LARGE (2)
      • MAX (1)
      • Min (2)
      • SMALL (3)
      • SUM (3)
      • SUMIF (1)
      • SUMIFS (2)
      • SUMPRODUCT (1)
    • Statistical (5)
      • COUNTIF (4)
      • COUNTIFS (1)
    • Texts and Strings (24)
      • CHAR (5)
      • CONCATINATE (1)
      • FIND (1)
      • LEFT (8)
      • Len (4)
      • MID (9)
      • REPT (3)
      • RIGHT (2)
      • SEARCH (9)
      • STRING (1)
      • SUBSTITUTE (2)
      • TEXT (4)
      • TRIM (2)
      • VALUE (2)
  • Uncategorized (2)
  • User-Defined f(x)s = UDF (3)
    • HyperlinkOf (1)
  • Worksheet (16)
  • XLfxs (2)

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
  • Count how many times letter found Formulas - combined functions
  • Show developer tab Excel User tips
  • Edit directly in cell Excel User tips
  • Paste Special Percentage Excel User tips
  • Formula Beautifier Excel User tips
  • Links between two workbooks – dynamically using functions INDIRECT
  • Scorring, saving multiple outputs in 1 number Basic Math
  • Convert cell into textbox Excel User tips

Copyright © 2025 Excel Functions.

Powered by PressBook News Dark theme