Xls Formula 46262

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...

Related files

Share

Help

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

Related files

Share

Share to social media

Help

Login Area