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
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
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.
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.
INDEX With MATCH best alternative function of VLOOKUP, HLOOKUP or LOOKUP fucntions.
How To Use INDEX with Match Watch Complete Video For Easy Learning