jagomart
digital resources
picture1_Xls Formula 46262 | Emt778


 206x       Filetype XLSX       File size 0.07 MB       Source: people.highline.edu


File: Xls Formula 46262 | Emt778
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 ...

icon picture XLSX Filetype Excel XLSX | Posted on 18 Aug 2022 | 3 years ago
Partial file snippet.
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

Sheet 2: 1
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











Sheet 3: 1an
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











The words contained in this file might help you see if this file matches what you are looking for:

...Sheet topics match functions basics index two way lookup one vertical left horizontal with column and orientated differently amp to do approximate on table sorted descending whole row cell reference dynamic range from more than when tables are same as formula a different the choose rows flip get amount in next bracket select random value first nonblank handle arrays that require cse basic tool used complex array formulas for extracting data function is returns relative position of an item list you tell look up tells what sort or empty aproximate ascending bigger bumped into then jump back if smaller na last it extact duplicates finds only can t find shows w exact dups not jo tom sue joe sioux st biggest fits gap lt x smallest...

no reviews yet
Please Login to review.