Working Let’s understand with an example of Sales table. We have a report (Figure1: Sales by Sales Person for the year 2017 and 2018) As you can see some of the values are blanks in Column G which is the Sales amount. You have to create a summary of quarterly Sales made by Sales Peron
Overview Sometimes you may need to look up the 2nd,3rd,4th or Nth value from a table i.e. based on the occurrences of the values dynamically lookup for the values. VLOOKUP () Function lookups a value from left side of the column and returns the first matching value based on column index number. We have to
Syntax =INDEX(data,MATCH(val,rows,0),MATCH(val,columns,0)) Let’s understand both the function INDEX function returns a value in a table based on the intersection of a row and column position within that table. Syntax =INDEX (array, row_num, [col_num], [area_num]) Arguments · array – A range of cells, or an array constant. · row_num – The row position in the reference or
Syntax =INDEX(data,MATCH(val,rows,0),MATCH(val,columns,0)) Let’s understand both the function INDEX function returns a value in a table based on the intersection of a row and column position within that table. Syntax =INDEX (array, row_num, [col_num], [area_num]) Arguments · array – A range of cells, or an array constant. · row_num – The row position in the reference or
Subject Line Use Exact () for case sensitive values Overview As you already know a usual VLOOKUP () formula is case sensitive and will ignore case. Sometimes you may want to lookup exact value in a table. Hence we use Exact () function along with INDEX & MATCH function with an array formula to retrieve
