Solving issues with "VLOOKUP"


VLOOKUP syntax
We often encounter "#N/A" while using VLOOKUP function to compare two lists. One of the most common problems that give rise to this error is extra spaces either to the end or to the beginning of the string.

 What's the way out?

 The simplest way out is to use "TRIM" function as a sub function.

Example

While trying to use VLOOKUP in the normal course, the result will be "#N/A", whereas with TRIM nested in the VLOOKUP function will give the positive result.

 (TRIM function removes the unwanted spaces in the beginning and end of a string.)

Comments

  1. UnknownDecember 29, 2015 at 6:01 PM

    Got it.... Thank you Boss

    ReplyDelete
  2. 123 essayDecember 21, 2016 at 8:05 PM

    Simply want to say your article is as surprising. The clarity on your put up is simply cool and i could suppose you're an expert in this subject.

    ReplyDelete
  3. App Development BangaloreJanuary 11, 2017 at 12:08 PM

    very informative post for me as I am always looking for new content that can help me and my knowledge grow better.

    ReplyDelete
  4. Buy contact lenses online in indiaJanuary 17, 2017 at 12:50 PM

    Hey keep posting such good and meaningful articles.

    ReplyDelete
  5. App Development MumbaiFebruary 3, 2017 at 6:16 PM

    What you're saying is completely true. I know that everybody must say the same thing, but I just think that you put it in a way that everyone can understand. I'm sure you'll reach so many people with what you've got to say.

    ReplyDelete
  6. GST Training DelhiOctober 9, 2017 at 11:05 AM

    Hey keep posting such sensible and significant articles.

    ReplyDelete
  7. App Devlopment CompanyJanuary 12, 2018 at 4:18 PM

    Nice post, things explained in details. Thank You.

    ReplyDelete
  8. AnonymousDecember 5, 2022 at 8:24 AM

    The original conversion was applied to roughly 50 late-model Bally slot machines. Because the everyday machine stopped the reels mechanically in lower than 10 seconds, weights were added to the mechanical timers to prolong the automated stopping of the reels. By the time the New Jersey Alcoholic Beverages Commission had accredited the conversion to be used in New Jersey arcades, the word was 온라인카지노 out and each other distributor began adding ability stops.

    ReplyDelete

Post a Comment

Popular posts from this blog

Keyboard Shortcut to add/delete rows or columns in Excel

Image
" Ctrl + + " (add rows or columns) and " Ctrl + - " (delete rows or columns) are the easy shortcuts one may remember while adding and deleting rows/columns in Microsoft Excel. Adding rows or columns: If you wish to add rows above the current cell... just press "shift + spacebar" (discussed in my earlier post) to select the current row and then press "Ctrl + +". It will insert a row over the current row. Press "Ctrl + +" repeatedly to insert as many rows. Similarly, "ctrl + spacebar" and then "Ctrl + +" adds a column before the current column. You may try pressing "Ctrl + +" without selecting a row or a column. In such a case, you may get a pop-up (as shown in the image) where you can choose an option of adding a row or a column. Deleting rows or columns: "shift + spacebar" followed by "Ctrl + -" deletes the selected row & "ctrl + spacebar" followed by "Ct...
Read more

Excel Keyboard Shortcuts - Handy during day to day work

I've had few posts earlier on few excel shortcuts. Navigation powered by "Ctrl" in Excel Shortcut to insert current date in a cell Shortcut to AutoFilter So, here I am, back with some more interesting shortcuts which will make your excel spreadsheet experience simpler. Let me start with some "CTRL" combinations which will be very handy for formating cells to match your requirements: CTRL + 1 : Opens the "Format Cells" dialog box CTRL + 2 : is to make the contents of the cell BOLD (this is a toggle key, to unbold use same CTRL + 2) CTRL + 3 : is to make the contents of the cell ITALICS (this is a toggle key) CTRL + 4 : is to underline the contents of the cell (this is a toggle key) CTRL + 5 : is to strike through the contents of the cell (this is a toggle key) CTRL + 9 : is to hide the entire row (to unhide use SHIFT + CTRL + 9) CTRL + 0 : is the hide the entire column (to unhide use SHIFT + CTRL + 0) CTRL + ~ : is to show or...
Read more

Alternative for SUMIF and SUMIFS - Part 2 (SUMPRODUCT)

Image
We already know the primary challenge of using SUMIF or SUMIFS i.e. unable to sum more than a single column or row as a sum range. Many of us in the course of our excel usage may have experienced that SUMIFS has a bigger disadvantage when used across workbooks i.e. the source workbook should be open inorder to enable to computation in the destination workbook. If the source workbook remains closed, the result in the destination workbook will be "#VALUE". In my previous post , the alternative suggested not only does overcome the  "#VALUE" challenge but also allow users to sum a larger range.  In this post, I was able to decipher yet another alternative. I'm sure expert users may already be using this however to help other users, this post can be a ray of hope. We may have used the SUMPRODUCT function to calculate weighted average. Extrapolating the usage of SUMPRODUCT with condition or conditions can help you achieve the SUMIFS goal.  Syntax:  =SU...
Read more