Microsoft Excel useful formulas

February 19, 2012   /   by Marco  / Categories :  Technology

I needed to filter and manupulate some data and I ended up using the below formulas which I wanted to share and note down:

strongVLOOKUP/strong

VLookup function searches for value in the left-most column of table_array and returns the value in the same row based on the index_number.

The syntax for the VLookup function is:

VLookup( value, table_array, index_number, not_exact_match )

value is the value to search for in the first column of the table_array.

table_array is two or more columns of data that is sorted in ascending order.

index_number is the column number in table_array from which the matching value must be returned. The first column is 1.

not_exact_match determines if you are looking for an exact match based on value. Enter FALSE to find an exact match. Enter TRUE to find an approximate match, which means that if an exact match if not found, then the VLookup function will look for the next largest value that is less than value.

For more info have a look at this website: a href=”http://www.techonthenet.com/excel/formulas/vlookup.php”http://www.techonthenet.com/excel/formulas/vlookup.php/a

strongCONCATENATE/strong

If you want to join several fields together then you can use formula.

The syntax for the strongConcatenate/strong function is:
blockquoteConcatenate( text1, text2, … text_n )/blockquote
There can be up to 30 strings that are joined together.

E.g: fi A1 = Business and A2 = Legions then

=Concatenate(A1, A2)

would return BusinessLegions

For more information have a look at this site: a href=”http://www.techonthenet.com/excel/formulas/concat.php”http://www.techonthenet.com/excel/formulas/concat.php/a

strongISERROR/strong

Instead of displaying the annoying #N/A error, this will just make the field empty.

the strongIsError/strong function can be used to check for error values.

The syntax for the strongIsError/strong function is:
blockquoteIsError( value )/blockquote
emvalue/em is the value that you want to test. If emvalue/em is an error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? or #NULL), this function will return TRUE. Otherwise, it will return FALSE.

For more information have a look at this site: a href=”http://www.techonthenet.com/excel/formulas/iserror.php”http://www.techonthenet.com/excel/formulas/iserror.php/a

There’s obviously a lot more exists in Excel and hopefully I can expand this list.

Here’s an example of combining the IF, ISERROR and VLOOKUP formulas together:

=IF(ISERROR(VLOOKUP($A5,[another_spreadsheet]A1:$D10273,2,FALSE)),””,VLOOKUP($A5,[another_spreadsheet]$A1:$D10273,2,FALSE))

DO YOU LIKE WHAT YOU'VE READ?
Join our subscription list and receive our content right in your mailbox. If you like to receive some Great deals our Freebies then subscribe now!

Our Sponsors

  • Follow us:

OTHER ARTICLES YOU MAY LIKE


USE FUSEBASE TO AUTOMATE TEAMWORK AND COLLABORATION

USE FUSEBASE TO AUTOMATE TEAMWORK AND COLLABORATION

If your business is growing, your team is juggling too many moving parts, and your client communication is spread across email threads, chat apps, documents, task boards, and endless follow ups, there is a good chance the real problem is not effort but fragmentation. Most businesses do not lose time because people are lazy. They […]

read more
USING REAP FOR VIDEO CLIPS

USING REAP FOR VIDEO CLIPS

Short form video is now one of the most effective ways to reach new audiences, grow brand awareness, and turn long form content into a steady stream of social media assets. For creators, marketers, consultants, educators, and business owners, the challenge is rarely a lack of content. The real problem is time. Editing long videos […]

read more

Like our Page