206x Filetype XLSX File size 0.07 MB Source: people.highline.edu
Sheet 1: Topics
1 | MATCH Functions Basics |
2 | INDEX Basics: Two way lookup |
3 | One way lookup vertical (lookup Left) |
4 | One way lookup horizontal |
5 | One way lookup with lookup column and match column orientated differently |
6 | INDEX & MATCH to do approximate lookup on table sorted Descending |
7 | Lookup whole row |
8 | Lookup whole column |
9 | Lookup Cell Reference |
10 | Dynamic Range |
11 | Lookup from more than one table when tables are on same sheet as formula |
12 | Lookup from more than one table when tables are on a different sheet than the formula: CHOOSE, INDEX, MATCH functions |
13 | INDEX and ROWS to Flip a Table |
14 | Get Amount in Next Bracket |
15 | Select a Random Value |
16 | Lookup first non-blank value |
17 | Handle arrays that require CSE |
18 | Basic lookup tool used in complex array formulas for extracting data |
MATCH function is a lookup function that returns the relative position of an item in a list | ||||||||||||
lookup_value is the value you tell the match function to lookup | ||||||||||||
lookup_array is the list that you look an item up in | ||||||||||||
[match_type] tells the MATCH what sort of lookup to do: | ||||||||||||
1 or empty = aproximate match; table sorted ascending; first bigger value bumped into then jump back one position, if value is smaller than first item returns #N/A, if bigger than last it returns last value | ||||||||||||
2 = extact match, if duplicates, it finds first one only, can't find it it shows #N/A | ||||||||||||
-1 = aproximate match; table sorted descending; first smaller value bumped into then jump back one position, if value is bigger than first item returns #N/A, if smaller than last it returns last value | ||||||||||||
Match w 0 (Exact Match) | ||||||||||||
Only 1 in list | Dups | Not in List | MATCH can do vertical or horizontal | |||||||||
Lookup | Jo | Tom | Sue | Jo | ||||||||
Relative Position | ||||||||||||
List 1 | List 1 | Joe | Jo | Tom | Sioux | Sioux | Sioux | Tom | List 2 | |||
Joe | 0 | |||||||||||
Jo | 1% | |||||||||||
Tom | 2% | |||||||||||
Sioux | 4% | |||||||||||
Sioux | 5% | |||||||||||
Sioux | 6% | |||||||||||
Tom | 8% | |||||||||||
MATCH with 1 or Empty & Sorted Ascending (Approximate Match - 1st Biggest & Jump Back) | ||||||||||||
Fits in Gap | Finds Exact Value | Smaller Than First | Bigger than Last | MATCH can do vertical or horizontal | ||||||||
Lookup | $150.00 | $100.00 | -$400.00 | $20,000.00 | $50.00 | |||||||
Relative Position | ||||||||||||
List 1 | List 1 | $0.00 | $100.00 | $500.00 | $1,000.00 | $2,500.00 | $5,000.00 | $10,000.00 | ||||
$0.00 | ||||||||||||
$100.00 | 100 <= x < 500 | |||||||||||
$500.00 | ||||||||||||
$1,000.00 | ||||||||||||
$2,500.00 | ||||||||||||
$5,000.00 | ||||||||||||
$10,000.00 | ||||||||||||
MATCH with -1 & Sorted Descending (Approximate Match - 1st Smallest & Jump Back) | ||||||||||||
Fits in Gap | Finds Exact Value | Bigger Than First | Smaller than Last | MATCH can do vertical or horizontal | ||||||||
Lookup | $1,500.00 | $2,500.00 | $20,000.00 | -$10.00 | $9,000.00 | |||||||
Relative Position | ||||||||||||
List 1 | List 1 | $10,000.00 | $5,000.00 | $2,500.00 | $1,000.00 | $500.00 | $100.00 | $0.00 | ||||
$10,000.00 | ||||||||||||
$5,000.00 | ||||||||||||
$2,500.00 | ||||||||||||
$1,000.00 | ||||||||||||
$500.00 | ||||||||||||
$100.00 | ||||||||||||
$0.00 |
MATCH function is a lookup function that returns the relative position of an item in a list | ||||||||||||
lookup_value is the value you tell the match function to lookup | ||||||||||||
lookup_array is the list that you look an item up in | ||||||||||||
[match_type] tells the MATCH what sort of lookup to do: | ||||||||||||
1 or empty = aproximate match; table sorted ascending; first bigger value bumped into then jump back one position, if value is smaller than first item returns #N/A, if bigger than last it returns last value | ||||||||||||
2 = extact match, if duplicates, it finds first one only, can't find it it shows #N/A | ||||||||||||
-1 = aproximate match; table sorted descending; first smaller value bumped into then jump back one position, if value is bigger than first item returns #N/A, if smaller than last it returns last value | ||||||||||||
Match w 0 (Exact Match) | ||||||||||||
Only 1 in list | Dups | Not in List | MATCH can do vertical or horizontal | |||||||||
Lookup | Jo | Tom | Sue | Jo | ||||||||
Relative Position | 2 | 3 | #N/A | 2 | ||||||||
List 1 | List 1 | Joe | Jo | Tom | Sioux | Sioux | Sioux | Tom | List 2 | |||
Joe | 0 | |||||||||||
Jo | 1% | |||||||||||
Tom | 2% | |||||||||||
Sioux | 4% | |||||||||||
Sioux | 5% | |||||||||||
Sioux | 6% | |||||||||||
Tom | 8% | |||||||||||
MATCH with 1 or Empty & Sorted Ascending (Approximate Match - 1st Biggest & Jump Back) | ||||||||||||
Fits in Gap | Finds Exact Value | Smaller Than First | Bigger than Last | MATCH can do vertical or horizontal | ||||||||
Lookup | $150.00 | $100.00 | -$400.00 | $20,000.00 | $50.00 | |||||||
Relative Position | 2 | 2 | #N/A | 7 | 1 | |||||||
List 1 | List 1 | $0.00 | $100.00 | $500.00 | $1,000.00 | $2,500.00 | $5,000.00 | $10,000.00 | ||||
$0.00 | ||||||||||||
$100.00 | ||||||||||||
$500.00 | ||||||||||||
$1,000.00 | ||||||||||||
$2,500.00 | ||||||||||||
$5,000.00 | ||||||||||||
$10,000.00 | ||||||||||||
MATCH with -1 & Sorted Descending (Approximate Match - 1st Smallest & Jump Back) | ||||||||||||
Fits in Gap | Finds Exact Value | Bigger Than First | Smaller than Last | MATCH can do vertical or horizontal | ||||||||
Lookup | $1,500.00 | $2,500.00 | $20,000.00 | -$10.00 | $9,000.00 | |||||||
Relative Position | 3 | 3 | #N/A | 7 | 1 | |||||||
List 1 | List 1 | $10,000.00 | $5,000.00 | $2,500.00 | $1,000.00 | $500.00 | $100.00 | $0.00 | ||||
$10,000.00 | ||||||||||||
$5,000.00 | ||||||||||||
$2,500.00 | ||||||||||||
$1,000.00 | ||||||||||||
$500.00 | ||||||||||||
$100.00 | ||||||||||||
$0.00 |
no reviews yet
Please Login to review.