I needed to filter and manupulate some data and I ended up using the below formulas which I wanted to share and note down:<\/p>\n

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.<\/p>\n

table_array is two or more columns of data that is sorted in ascending order.<\/p>\n

index_number is the column number in table_array from which the matching value must be returned. The first column is 1.<\/p>\n

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.<\/p>\n

For more info have a look at this website:\u00a0a href=”http:\/\/www.techonthenet.com\/excel\/formulas\/vlookup.php”http:\/\/www.techonthenet.com\/excel\/formulas\/vlookup.php\/a<\/p>\n

The syntax for the\u00a0strongConcatenate\/strong\u00a0function is:
\nblockquoteConcatenate( text1, text2, … text_n )\/blockquote
\nThere can be up to 30 strings that are joined together.<\/p>\n

For more information have a look at this site:\u00a0a href=”http:\/\/www.techonthenet.com\/excel\/formulas\/concat.php”http:\/\/www.techonthenet.com\/excel\/formulas\/concat.php\/a<\/p>\n

Instead of displaying the annoying #N\/A error, this will just make the field empty.<\/p>\n

the\u00a0strongIsError\/strong\u00a0function can be used to check for error values.<\/p>\n

The syntax for the\u00a0strongIsError\/strong\u00a0function is:
\nblockquoteIsError( value )\/blockquote
\nemvalue\/em\u00a0is the value that you want to test. If\u00a0emvalue\/em\u00a0is an error value (#N\/A, #VALUE!, #REF!, #DIV\/0!, #NUM!, #NAME? or #NULL), this function will return TRUE. Otherwise, it will return FALSE.<\/p>\n

For more information have a look at this site:\u00a0a href=”http:\/\/www.techonthenet.com\/excel\/formulas\/iserror.php”http:\/\/www.techonthenet.com\/excel\/formulas\/iserror.php\/a<\/p>\n

There’s obviously a lot more exists in Excel and hopefully I can expand this list.<\/p>\n

Here’s an example of combining the IF, ISERROR and VLOOKUP formulas together:<\/p>\n

=IF(ISERROR(VLOOKUP($A5,[another_spreadsheet]A1:$D10273,2,FALSE)),””,VLOOKUP($A5,[another_spreadsheet]$A1:$D10273,2,FALSE))<\/p>\n

DO YOU LIKE WHAT YOU'VE READ?<\/div>

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!\r\n\r\n

\n\n

Name<\/label>\n<\/div>\n

Email<\/label>\n<\/div>\n

\n<\/div>\n<\/form>\n<\/div>\n<\/p>\r\n\r\n<\/div><\/div>\n

<\/div>","protected":false},"excerpt":{"rendered":"

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: […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[441,443,442,446,450,438,433,417,448,437],"class_list":["post-312","post","type-post","status-publish","format-standard","hentry","category-technology","tag-array-index","tag-ascending-order","tag-column-number","tag-error-values","tag-excel-formulas","tag-index-number","tag-legions","tag-microsoft-excel","tag-techonthenet","tag-vlookup-function"],"_links":{"self":[{"href":"https:\/\/www.businesslegions.com\/blog\/wp-json\/wp\/v2\/posts\/312","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.businesslegions.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.businesslegions.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.businesslegions.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.businesslegions.com\/blog\/wp-json\/wp\/v2\/comments?post=312"}],"version-history":[{"count":2,"href":"https:\/\/www.businesslegions.com\/blog\/wp-json\/wp\/v2\/posts\/312\/revisions"}],"predecessor-version":[{"id":314,"href":"https:\/\/www.businesslegions.com\/blog\/wp-json\/wp\/v2\/posts\/312\/revisions\/314"}],"wp:attachment":[{"href":"https:\/\/www.businesslegions.com\/blog\/wp-json\/wp\/v2\/media?parent=312"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.businesslegions.com\/blog\/wp-json\/wp\/v2\/categories?post=312"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.businesslegions.com\/blog\/wp-json\/wp\/v2\/tags?post=312"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}