Useful Excel functions for a SOA resource

VLOOKUP Function:

One simple requirement for VLOOKUP function is to compare two sheet and find out the matching rows.

For example here we have two tables of Order ID’s and we want to match them (get matching and not matching ID’s).

We can copy both the list in same or to be more clear in different sheets.

                                            

On an available cell, we need to write the VLOOKUP function as below, So this lookups for a value in

 an array range and if found returns a column of that matched row.

Range lookup = true – approx. match will be returned

Range lookup = false – exact match will be returned

range_lookup   (optional)

A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match: ·         TRUE assumes the first column in the table is sorted either numerically or alphabetically, and will then search for the closest value. This is the default method if you don't specify one. ·         FALSE searches for the exact value in the first column.

 

So below function fulfils our purpose.

 

But there is one complexity, we don’t want to write this function for each cell, but rather drag it to reflect in all cell, but when we drag and copy cell it increases all cell values even the range.

But we want to keep range fixed:

=VLOOKUP(A3,Table2!A3:A18,1,FALSE)

To resolve this add a couple of $ as shown below and it will fix the values of range, you are good to drag now, it will only increase the lookup value.

=VLOOKUP(A2,Table2!$A$2:$A$17,1,FALSE)

 

To Split a cell:

Sometimes we need to split a cell to extract a value. For example here we need to get just the order Id and need to get rid of any extra text.

In this case our requirement is to just split it as fixed length, but we can do this for delimited values as well.

 

Select the whole column, go to the Data tab present in the top ribbon and select Text to columns.

 

Drag the arrows from left side to the position where you want to make the cut. 

Finish the process and you are done.