VLOOKUP in Excel Tips and Tricks

We already learned on how to use VLOOKUP in Excel.  In my earlier different posts, i have shown, the different ways you can use vlookup functions. This post is the summarisation all the things which we have already discussed.

VLOOKUP in Excel Tips and Tricks

Here is the list of few VLOOKUP in Excel tips to use the excel worksheet function more effectively.

1. Use named Ranges:  the vlookup function with normal range variables looks very complicated. To avoid this, use named ranges.  With named ranges, you can do dynamic ranges which saves lot of memory and by that, the time. You can read more dynamic ranges in excel here.
2. 2 way or conditional vlookup: The default vlookup accepts only single criteria lookup. But using the match function, you use vlookup function which looks for the given value in row as well as in columns. The syntax is same as the normal vlookup apart from inserting match function instead of index number. The match function returns the reference number of the column. To learn more on this read my post on conditional vlookup.
3. Handling error values in Excel: When the lookup value is not found, vlookup returns, #na. You can remove this by using iferror function. This will make your excel template more clean and easy to perform calculations
=IFERROR(VLOOKUP(B16,A2:C6,2,FALSE),0)
4. Use Wildcard characters for related lookup: You can use related lookup using the last argument as TRUE. but, it has a downside. The range should be in ascending order. The wildcard character will help in these situations. Below are the few examples.
=VLOOKUP(“Galaxy*”,A2:C8,3,FALSE)- returns the value from the range which starts with Galaxy. If there are multiple Galaxy are there, then it will take the first one.
=VLOOKUP(“????”,A2:C8,3,FALSE) this look for a value with the lookup value length is 4.
Similarly you can use most of the wildcard characters in vlookup also.

If you find these tips on VLOOKUP in Excel, or have any queries, feel free to leave feedback in the comment section below.

(Visited 515 times, 1 visits today)

Meet the Author

Hi, I am Aniruddha, an avid blogger since 2008 who likes writing about new gadgets, smartphones and excel. You can find them at www.tech2touch.com. You can also follow me on Twitter @tech2touch or my Facebook page tech2touch. You can subscribe to my YouTube channel tech2touch. Enjoy reading and watching!!!

0 comments… add one

Leave a Comment

Shares