Showing posts with label MATCH. Show all posts
Showing posts with label MATCH. Show all posts

HOW TO USE INDEX / MATCH FUNCTION IN EXCEL

HOW TO USE INDEX AND MATCH FUNCTION IN EXCEL

Users Heavily use Vlookup or Lookup Function for Reference. I proved INDEX / MATCH much powerful and easy function against VLOOKUP. how to use INDEX and MATCH function.

How to use INDEX Function 

The INDEX Formula returns either the value from range or table.
The INDEX function have 2 Syntaxes

Syntax 

=INDEX(array,row_num,[column_num])

array: A Range of a table or Cells
row_num: The row number in the array to use to return the value.
column_number: Optionally, it is the column number in the array to use to return the value

How To Use Index Function In Excel

 in this example our required is sales value of code 1004 or product Book, in Cell C10 first syntax array is C2:C7 value. Second syntax is row_number after indexed code 1004 position is 4.

How to use MATCH Function

MATCH function looks for an item in a list and shows its position. it can be used with text and numbers. it can look for an exact match or an approximate match. 

Syntax


=MATCH(lookup_value,lookup_array,[match_type])

lookup_value. The value that you want to match in lookup_array.
lookup_array. The range of cells being searched.
match_type Optional. The number -1, 0, or 1.



The match-type either 0, 1 or -1

using 0 will look for an exact match. if no match is found the #N/A error will be shown.

using 1 will look for an exact match, or the next lowest number if  no exact match exists. if there is no match or next lowest number the error #N/A is shown. The list of values being examined must be sorted for this to work correctly 

using -1 will look for an  exact match, or the next highest number if no exact match exists. if there is no exact match or next highest number the error #N/A is shown. The list must be sorted for this to work properly. 

How To use match Function in Excel

INDEX With MATCH best alternative function of VLOOKUP, HLOOKUP or LOOKUP fucntions.

How To Use INDEX with Match Watch Complete Video For Easy Learning